sqlskills-logo-2015-white.png

Search Engine Q&A #7: Restoring from a backup file containing multiple database backups


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

2 thoughts on “Search Engine Q&A #7: Restoring from a backup file containing multiple database backups”

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course