In Recovery...

This is another question that came up on the Disaster Recovery forum on MSDN. Paraphrasing – ‘ I have a backup file containing full backups for 45 databases. How can I restore them all using a script?’

The answer is pretty straightforward. Let’s create the situation described, using 3 databases for clarity rather than 45:

The file ‘c:\tinylogtest\tinylogtest.mdf’ cannot be overwritten. It is being used by database ‘tinylogtest’.

Msg 3156, Level 16, State 4, Line 1

File ‘fgt_mdf’ cannot be restored to ‘c:\tinylogtest\tinylogtest.mdf’. Use WITH MOVE to identify a valid location for the file.

Msg 1834, Level 16, State 1, Line 1

The file ‘c:\tinylogtest\tinylogtest.ldf’ cannot be overwritten. It is being used by database ‘tinylogtest’.

Msg 3156, Level 16, State 4, Line 1

File ‘fgt_log’ cannot be restored to ‘c:\tinylogtest\tinylogtest.ldf’. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 1

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

So how to restore the other databases apart from the first one? The answer is that you need to specify which of the backups within the file you’re after. You can find the positions using the RESTORE HEADERONLY command:

RESTORE

HEADERONLYFROMDISK=‘c:\sqlskills\mixedbackups.bck’;

GO

This returns lots of information:

and then a whole bunch more columns and then ending with:

For our purposes, the interesting things to note are the Position, DatabaseName, and BackupTypeDescription. In our simple example we have 3 full backups. To restore the pagesplittest database as we tried to do above, we have to specifiy the position within the file of the backup we want to restore – just the database name isn’t enough.

And it works. So the question asked how to do this, plus how to do it in a script. Below is a script I’ve adapted from the example I wrote for the Books Online for DBCC SHOWCONTIG back in 1999 when I rewrote DBCC SHOWCONTIG for SQL Server 2000.