First I asked my question here: Migrate all databases from one SQL Server 2005 to another

Many friends replied to it and I gathered lots of useful information.

I have another scenario in which we can have some hours of downtime. We want to close the SQL Server port on the firewall, backup the databases, move the backups to new server, restore them on our new server.

It can be achieved by creating backup of all databases including master DB. But in this case we will have 200 .bak files.

Is there any way to have a general .bak file including all of the data on our SQL Server? So we will easily restore this big .bak file in our new server?

Is there any internal option in SQL Server 2005 to restore the whole date on our new SQK Server 2005?

Thank you for your replies.

  • How do you propose to transfer a big backup file of 200 database at one go from PR to DR. Do you have such reliable network ?. I would not suggest you to drop idea of consolidated backup and move backup piecemeal. – Shanky Sep 27 '14 at 11:27
  • How will one backup file help you? Even if you have all 200 database backups in one file, you'll still need to write 200 restore statements. – James L Sep 27 '14 at 12:30

There is no built-in way to backup all the databases into a single file, and even if there was, that file would probably become impractically big.

I would go with the ideas you got from your original question, and implement those in an automated script that loops through all your databases; One script to back every database up, one script to restore those backups on the new server. Here's an idea on how to build such a script:

SELECT 'BACKUP DATABASE '+QUOTENAME([name])+' TO  DISK = N''X:\yz\.....'''
FROM master.sys.databases
WHERE [name] NOT IN ('master', 'tempdb', 'model', 'msdb');

Your Answer

