Backup the whole SQL Server databases in one .bak file - SQL Server 2005

Question

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.

Answer 1

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');