I have to copy about 40 databases in one server to a different. The brand new databases must have new names, but the same tables, data and indexes because the original databases. To date I have been:
1) creating each destination database
2) while using "Tasks->Export Data" command to produce and populate tables for every database individually
3) repairing all the indexes for every database having a SQL script
Only three steps per database, but I'll wager there's an simpler way. Inflict MS SQL Server experts available have advice?
Maybe the simplest would be to remove/reattach. Right-click within the server manager around the DB, tasks --> remove. Then copy the MDF/LDF files towards the new server after which reattach by hitting the server icon and tasks-->attach. It'll request you for that MDF file - make certain the title etc is accurate.
Considering that you are carrying out this on multiple databases -- you'll need a simple scripted solution, not really a point and click on solution.
This can be a backup script which i keep around. Have it employed by one file after which modify it for a lot of.
(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
Backup -> Restore may be the easiest, otherwise to make use of the replication.
So as of ease
- stop server/fcopy/attach is most likely simplest.
- backup/restore - can be achieved disconnected pretty easy and simple
- transfer DTS task - needs file copy permissions
- replication - farthest from easy to setup
Items to consider permissions, customers and groups in the destination server esp. if you are moving or rebuilding.
You will find better solutions already but it is really an 'also ran' since it is yet another option.
For that low low cost of free you could think about the Microsoft SQL Server Database Publishing Wizard. It enables you to definitely script the schema, data or data and schema. Plus is could be run from the UI or command line <- think CI process.
Backup the databases while using standard SQL backup tool in Enterprise Manager, when you restore around the second server you are able to specify the title from the new database.
This really is the easiest method to keep up with the schema in the whole.
If you are using the Backup/Restore solution you are prone to have orphaned customers so make sure to take a look at this article<microsoft> regarding how to fix them.
use backup copies to revive the databases towards the new server using the new names.
Redgate SQL Compare and SQL Data Compare. The Comparison Bundle was undoubtedly the very best investment a business I labored for good made. Moving e-training content would be a breeze by using it.
A different one to look at that's simple and quick:
Simple SQL BULK Copy