1:00 AM
Paul B
At my day job we are getting ready to move our hosting environment from one colocation facility to another. Sounds easy enough right? Well it is if you can afford some down time, or of you have some money to buy more equipment to replicate your environment. If you are working on a shoe string budget though, then you have to get creative.
One of the things I have been working on lately is migrating SQL data from a very large Microsoft SQL server that has multiple instances, each with about 100 databases or so. These four instances were originally stored on one physical server with no redundancy. Bad idea right? Well, at our new data center I have configured two fail-over SQL clusters to house all of this data. I am moving two instance per cluster. I would have built two more clusters, but I just don't have enough SAN space at the new data center yet. Anyway, In order to migrate the data relatively quickly, I hooked up a USB hard drive to the physical SQL server and created a maintenance job on each instance to backup all the databases to the USB drive. Now with all that data on the USB drive, I drove it over to the new data center, and hooked it up to a server there and shared it out. Now the tricky part, restoring all those friggin' databases! All friggin' 400 some odd databases!
Now I'm not a DBA, so I don't know T-SQL scripting at all, but I was able to figure out something. If you go through GUI (Microsoft SQL Manager) and go through all the steps to do the restore, right before you restore there is an option so save that job as a T-SQL script! Boom!
So now that I have gone through the motions of restoring one, I get a little bit of code similar to this:
Cool, now all I had to do to restore multiple databases was to copy and paste the above code (Before the GO line) over and over again, and changing the database information to match the other database names and backup files. For example, restoring three databases at once would look like this:
Make sure that if you are using the above code to change it to fit your environment!
Now if you know of an easier, or better way of doing this, I am all ears. All though creating the script was fairly easy, it was tedious as a mother f**ker to copy and paste all the info needed for 400 some odd databases. Please let me know in the comments if you have done this before, and did it differently.
One of the things I have been working on lately is migrating SQL data from a very large Microsoft SQL server that has multiple instances, each with about 100 databases or so. These four instances were originally stored on one physical server with no redundancy. Bad idea right? Well, at our new data center I have configured two fail-over SQL clusters to house all of this data. I am moving two instance per cluster. I would have built two more clusters, but I just don't have enough SAN space at the new data center yet. Anyway, In order to migrate the data relatively quickly, I hooked up a USB hard drive to the physical SQL server and created a maintenance job on each instance to backup all the databases to the USB drive. Now with all that data on the USB drive, I drove it over to the new data center, and hooked it up to a server there and shared it out. Now the tricky part, restoring all those friggin' databases! All friggin' 400 some odd databases!
Now I'm not a DBA, so I don't know T-SQL scripting at all, but I was able to figure out something. If you go through GUI (Microsoft SQL Manager) and go through all the steps to do the restore, right before you restore there is an option so save that job as a T-SQL script! Boom!
So now that I have gone through the motions of restoring one, I get a little bit of code similar to this:
RESTORE DATABASE [DB_RESTORE] FROM DISK = N'\\<FILESERVER>\<FILESHARE>\DB_Backup.bak' WITH FILE = 1, MOVE N'DB_Backup' TO N'E:\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_RESTORE.mdf', MOVE N'DB_Backup_log' TO N'E:\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_RESTORE_1.LDF', NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [DB_RESTORE1] FROM DISK = N'\\<FILESERVER>\<FILESHARE>\DB_Backup1.bak' WITH FILE = 1, MOVE N'DB_Backup1' TO N'E:\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_RESTORE1.mdf', MOVE N'DB_Backup1_log' TO N'E:\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_RESTORE1_1.LDF', NOUNLOAD, STATS = 10After I had my script in order, all I had to do after that was to create another maintenance job on the new cluster to run my T-SQL script above. I had to set the timeout to about 60000, and just let it run. After about ten minutes or so I had all the databases restored! It actually took me longer to generate the script than it did to restore all the databases!
RESTORE DATABASE [DB_RESTORE2] FROM DISK = N'\\<FILESERVER>\<FILESHARE>\DB_Backup2.bak' WITH FILE = 1, MOVE N'DB_Backup2' TO N'E:\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_RESTORE2.mdf', MOVE N'DB_Backup2_log' TO N'E:\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_RESTORE2_1.LDF', NOUNLOAD, STATS = 10
RESTORE DATABASE [DB_RESTORE3] FROM DISK = N'\\<FILESERVER>\<FILESHARE>\DB_Backup3.bak' WITH FILE = 1, MOVE N'DB_Backup3' TO N'E:\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_RESTORE3.mdf', MOVE N'DB_Backup3_log' TO N'E:\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_RESTORE3_1.LDF', NOUNLOAD, STATS = 10
GO
Make sure that if you are using the above code to change it to fit your environment!
Now if you know of an easier, or better way of doing this, I am all ears. All though creating the script was fairly easy, it was tedious as a mother f**ker to copy and paste all the info needed for 400 some odd databases. Please let me know in the comments if you have done this before, and did it differently.
