question

himabindu avatar image
himabindu asked

SQL Server migration

I have to migrate 6 server from sql server 2005 to sql server 2008. Do I have to go to each and every database in each server and then move them to the new one. Or is there any way that I could migrate the whole server with all the databases in it at a strech?
sql-server-2008sql-server-2005migration
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
You could write a script to backup each database from the 2005 server and then another to restore each one on the 2008 server but from my experience it is much better to work on each database in turn, making sure that each one gets all of its settings configured correctly and that the systems that attach to each are all properly functioning. Its better to have one system offline at a time than many
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
As fatherjack already answered, a script to do backup and restore of all databases, but I just want to add some improvements. Make sure you have full recovery mode, do a full backup and make sure you have log backups, let the production databases to be online while you do the restore without recovery. When all databases are restored, apply the log backups, shut down the production server and apply the last transaction backups in the new server and recover the databases. By doing this, you will have a very short downtime. Make sure you test this procedure so you feel comfortable when it's time to do it for real.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Adding to @Fatherjack 's and @Håkan Winther 's answers: Unless you already thought about it - you probably want to transfer the logins as well. Here's how you could do that: [ http://support.microsoft.com/kb/918992][1] The article contains two stored procedures, which you should create in your SOURCE server, and run **sp_help_revlogin** proc. The output from that proc is a SQL script to run on the TARGET server. You want to run that script after you have created the databases in the new instance, otherwise the **CREATE LOGIN [..] WITH DEFAULT_DATABASE=[someDB]** statement will fail. [1]: http://support.microsoft.com/kb/918992
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
Have you considered database mirroring or log shipping for this?? That way, you have the source and target servers running, all the dbs get copied across to the target and are kept up to date. You then need to do login transfers and jobs (script them out). Once the target is in sync you do the "soft-failover" to the target box. With mirroring you just tell the dbs to switch to the mirror and you're done! You can then remove mirroring and everything is on the new box. That is obviously a high-level run down of how to do it, but that would give you a possible downtime of minutes instead of hours.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.