[ACCEPTED]-How best to copy entire databases in MS SQL Server?-database
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...)
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
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.
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.
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.
Backup -> Restore is the simplest, if not 1 to use the replication.
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.
Another one to check out that is quick and 1 simple:
Simple SQL BULK Copy
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.
use backups to restore the databases to 1 the new server with the new names.
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.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.