Menu
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?
784
How to copy a database on the same SQL server. 1 1. step: Make a back up of your source database. Click on the desired database and choose “Backup” under tasks. 2 2. step: Use copy only or use a full backup. 3 3. step: Use “Restore” to create a new database. 4 4. step: Choose the copy-only backup and choose a new name.
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: 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?
If you can login to both servers (the Express and the 05 Server) using SQL Server Management Studio then you can do a DB Restore from one database to the other. No need for backup files at all.
Another method that can be used to copy tables from the source database to the destination one is the SQL Server Export and Import wizard, which is available in SQL Server Management Studio.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
