How best to copy entire databases in MS SQL Server?

Question

I need to copy about 40 databases from one server to another. The new databases should have new names, but all the same tables, data and indexes as the original databases. So far I've been:

1) creating each destination database
2) using the "Tasks->Export Data" command to create and populate tables for each database individually
3) rebuilding all of the indexes for each database with a SQL script

Only three steps per database, but I'll bet there's an easier way. Do any MS SQL Server experts out there have any advice?

Answer 1

Backup -> Restore is the simplest, if not to use the replication.

Answer 2

Backup the databases using the standard SQL backup tool in Enterprise Manager, then when you restore on the second server you can specify the name of the new database.

This is the best way to maintain the schema in its entirety.

Answer 3

use backups to restore the databases to the new server with the new names.

Answer 4

Maybe the easiest is to detach/reattach. Right-click in the server manager on the DB, tasks --> detach. Then copy the MDF/LDF files to the new server and then reattach by clicking on the server icon and tasks-->attach. It will ask you for the MDF file - make sure the name etc is accurate.

Answer 5

Given that you're performing this on multiple databases -- you want a simple scripted solution, not a point and click solution.

This is a backup script that i keep around. Get it working for one file and then modify it for many.

(on source server...)
BACKUP DATABASE Northwind
  TO DISK = 'c:\Northwind.bak'

(target server...)
RESTORE FILELISTONLY
  FROM DISK = 'c:\Northwind.bak'

(look at the device names... and determine where you want the mdf and
ldf files to go on this target server)

RESTORE DATABASE TestDB
  FROM DISK = 'c:\Northwind.bak'
  WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
  MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

Answer 6

In order of ease

• stop server/fcopy/attach is probably easiest.
• backup/restore - can be done disconnected pretty simple and easy
• transfer DTS task - needs file copy permissions
• replication - furthest from simple to setup

Things to think about permissions, users and groups at the destination server esp. if you're transferring or restoring.

Answer 7

Redgate SQL Compare and SQL Data Compare. The Comparison Bundle was by far the best investment a company I worked for ever made. Moving e-training content was a breeze with it.

Answer 8

There are better answers already but this is an 'also ran' because it is just another option.

For the low low price of free you could look at the Microsoft SQL Server Database Publishing Wizard. This tool allows you to script the schema, data or data and schema. Plus is can be run from a UI or command line <- think CI process.

Answer 9

If you use the Backup/Restore solution you're likely to have orphaned users so be sure to check out this article<microsoft> on how to fix them.

Answer 10

Check those links:

• For multiple db's backup
• and single db restore

Answer 11

Another one to check out that is quick and simple:

Simple SQL BULK Copy

http://projects.c3o.com/files/3/plugins/entry11.aspx