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?
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
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.
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] 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. :
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.