Unanswered: Restoring multiple databases from a single .bak
I have a single .bak file containing full backups of 20+ databases.
Whats the best way of creating and restoring these database onto an instance on a separate server. A script or procedure will be greatly appreciated.
Please note that this is to migrate the whole instance of sql server onto a newly build server. Any guidance re steps will be appreciated.
not knowing the order in which the databases were backed up will be a bit of a pain, but it can be done. use RESTORE HEADERONLY and redirect the output to a table to get the list of database names, and file ids that correspond to their backups. Then loop through that table, and execute the restore database command with the appropriate database name and file id parameters.
I doubt anyone has a script for this, because it is a bit of a rare need. Will you be doing this often?