I have a SQL Server 2005 instance on my PROD server. I have been given a task to migrate databases from that instance to a different box (SQL Server 2008 R2 on Windows 2008 R2). The current instance has a maintenance plan set up, which backs up 8 different databases every night to a single .bak file.
How do I take that bak file and restore it onto the new SQL Server 2008 R2 instance? I have tried using SQL Server 2008 R2 SSMS, and set the restore from option to device and point it to that .bak file, but what should I target restore to since I have multiple databases to be restored in the new instance?
What is the best approach to migrate all databases in single shot to the new instance without doing one at a time?
3 Answers 3
First thing i would like to highlight is you should have the system backups in addition to user databases handy when you said you need to migrate from SQL2k5 to SQL2k8R2.
You can copy all the latest backups from the current server onto the Windows2K8 box you mentioned.
Once copied, the first thing you should be looking is restoring the system database backups as mentioned here Backup and Restore system databases
Doing the above will make sure all the logins, SSIS packages, jobs etc would be attached as well when migrating to new version.
Now, you should start restoring the user database backups copied from old server to this new box as guided in Restore database from existing full backups
In addition to above you can use Powershell to completely migrate all the databases in go as you mentioned , from here.
Also, you can refer Migrate SQL server from one machine to another
For first you can configure log shipping to a new box.
So at any given time you can switch to new box fast, but it will be impossible to revert to an old box if something happened after you have started to work with databases on a new box.
What is the best approach to migrate all databases in single shot to the new instance without doing one at a time?
Log Shipping is a very effective approach for a situation like this...
- Configure Log Shipping to the new server for all 8 databases.
- When ready to migrate, use this solution to fail over all databases at the same time. Run the script on the Secondary and use the resulting scripts 01.sql - 05.sql to fail over to the Secondary.
The solution above will kill all connections to the Primary databases, take a final log backup for each, and leave the databases in NORECOVERY mode. After applying those log backups on the Secondary it will bring up the databases so they are ready for your application(s).
Ensure you prepare for the Failover by copying Logins, Jobs, etc. prior to the migration.