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:
BACKUP
DATABASE tinylogtest TO DISK = 'c:\sqlskills\mixedbackups.bck' WITH NAME = 'tinylogtest 10/09/07', INIT;
BACKUP
DATABASE pagesplittest TO DISK = 'c:\sqlskills\mixedbackups.bck' WITH NAME = 'pagesplittest 10/09/07';
BACKUP
DATABASE ghostrecordtest TO DISK = 'c:\sqlskills\mixedbackups.bck' WITH NAME = 'ghostrecordtest 10/09/07';
GO
You now can't do a regular restore from that backup file for any database except the first one in the file:
RESTORE
DATABASE pagesplittest FROM DISK = 'c:\sqlskills\mixedbackups.bck' WITH REPLACE;
GO
Msg 1834, Level 16, State 1, Line 1
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
HEADERONLY FROM DISK = '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.
RESTORE
DATABASE pagesplittest FROM DISK = 'c:\sqlskills\mixedbackups.bck' WITH REPLACE, FILE = 2;
GO
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.
Enjoy!
-- Create a temporary table to hold the output from RESTORE HEADERONLY
CREATE TABLE master.dbo.restoreheaderonly (
BackupName
NVARCHAR (128), BackupDescription NVARCHAR (255), BackupType SMALLINT, ExpirationDate DATETIME,
Compressed TINYINT, Position SMALLINT, DeviceType TINYINT, UserName NVARCHAR (128), ServerName NVARCHAR (128),
DatabaseName NVARCHAR (128), DatabaseVersion INT, DatabaseCreationDate DATETIME, BackupSize NUMERIC (20, 0),
FirstLSN NUMERIC (25, 0), LastLSN NUMERIC (25,0), CheckpointLSN NUMERIC (25,0), DatabaseBackupLSN NUMERIC (25, 0),
BackupStartDate
DATETIME, BackupFinishDate DATETIME, SortOrder SMALLINT, CodePage SMALLINT, UnicodeLocaleId INT,
UnicodeComparisonStyle INT, CompatibilityLevel TINYINT, SoftwareVendorId INT, SoftwareVersionMajor INT,
SoftwareVersionMinor INT, SoftwareVersionBuild INT, MachineName NVARCHAR (128), Flags INT, BindingID UNIQUEIDENTIFIER,
RecoveryForkID UNIQUEIDENTIFIER, Collation NVARCHAR (128), FamilyGUID UNIQUEIDENTIFIER, HasBulkLoggedData BIT,
IsSnapshot BIT, IsReadOnly BIT, IsSingleUser BIT, HasBackupChecksums BIT, IsDamaged BIT, BeginsLogChain BIT,
HasIncompleteMetaData BIT, IsForceOffline BIT, IsCopyOnly BIT, FirstRecoveryForkID UNIQUEIDENTIFIER,
ForkPointLSN NUMERIC (25, 0) NULL, RecoveryModel NVARCHAR (60), DifferentialBaseLSN NUMERIC (25, 0) NULL,
DifferentialBaseGUID UNIQUEIDENTIFIER, BackupTypeDescription NVARCHAR (60), BackupSetGUID UNIQUEIDENTIFIER NULL);
GO
-- Populate the table
INSERT INTO master.dbo.restoreheaderonly EXEC ('RESTORE HEADERONLY FROM DISK = ''C:\sqlskills\mixedbackups.bck''') ;
GO
DECLARE
@Position SMALLINT;
DECLARE
@DatabaseName NVARCHAR (128);
DECLARE
@ExecString NVARCHAR (255);
-- Declare a cursor to iterate over the results
DECLARE databases CURSOR FOR
SELECT Position, DatabaseName FROM master.dbo.restoreheaderonly WHERE BackupTypeDescription = 'Database';
-- Open the cursor.
OPEN
databases;
-- Loop through the databases.
FETCH
NEXT FROM databases INTO @Position, @DatabaseName;
WHILE
@@FETCH_STATUS = 0
BEGIN
SELECT @ExecString = 'RESTORE DATABASE ' + RTRIM (@DatabaseName) +
' FROM DISK = ''C:\sqlskills\mixedbackups.bck''' +
' WITH REPLACE, FILE = ' + RTRIM (CONVERT (VARCHAR (10), @Position));
SELECT 'Restoring database ' + RTRIM (@DatabaseName);
EXEC (@ExecString);
FETCH NEXT FROM databases INTO @Position, @DatabaseName;
END
;
-- Close and deallocate the cursor.
CLOSE
databases;
DEALLOCATE
databases;
-- Delete the temporary table.
DROP
TABLE master.dbo.restoreheaderonly;
GO