[ACCEPTED]-How best to copy entire databases in MS SQL Server?-database

Accepted answer
Score: 47

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

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

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

(target server...)
  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)

  FROM DISK = 'c:\Northwind.bak'
  WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
  MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
Score: 14

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

Score: 4

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 2 and groups at the destination server esp. if 1 you're transferring or restoring.

Score: 4

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 4 look at the Microsoft SQL Server Database Publishing Wizard. This tool allows you to script 3 the schema, data or data and schema. Plus 2 is can be run from a UI or command line 1 <- think CI process.

Score: 2

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

Score: 2

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

Score: 2

Another one to check out that is quick and 1 simple:

Simple SQL BULK Copy


Score: 1

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

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

Score: 0

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

Score: 0

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

Score: 0

More Related questions