Restore All SQL Databases from a Backup File

by Joe Havelick 5. July 2011 09:22

As part of a  disaster recovery plan, I recently needed to have a script to restore all the databases, from all the backups (Full and Differential) from a single backup file. Here's how I did it:

 

SET NOCOUNT ON;

DECLARE @DBName nvarchar(256)
DECLARE @BackupFile nvarchar(256) = N'\\SERVER\PATH\FILE.bak' 
DECLARE @FileID int

DECLARE @BackupHeaders TABLE
      (
      BackupName nvarchar(128) NULL,
      BackupDescription  nvarchar(255) NULL,
      BackupType smallint NULL,
      ExpirationDate datetime NULL,
      Compressed tinyint NULL,
      Position smallint NULL,
      DeviceType tinyint NULL,
      UserName nvarchar(128) NULL,
      ServerName nvarchar(128) NULL,
      DatabaseName nvarchar(128) NULL,
      DatabaseVersion int NULL,
      DatabaseCreationDate  datetime NULL,
      BackupSize numeric(20,0) NULL,
      FirstLSN numeric(25,0) NULL,
      LastLSN numeric(25,0) NULL,
      CheckpointLSN  numeric(25,0) NULL,
      DatabaseBackupLSN  numeric(25,0) NULL,
      BackupStartDate  datetime NULL,
      BackupFinishDate  datetime NULL,
      SortOrder smallint NULL,
      CodePage smallint NULL,
      UnicodeLocaleId int NULL,
      UnicodeComparisonStyle int NULL,
      CompatibilityLevel  tinyint NULL,
      SoftwareVendorId int NULL,
      SoftwareVersionMajor int NULL,
      SoftwareVersionMinor int NULL,
      SoftwareVersionBuild int NULL,
      MachineName nvarchar(128) NULL,
      Flags int NULL,
      BindingID uniqueidentifier NULL,
      RecoveryForkID uniqueidentifier NULL,
      Collation nvarchar(128) NULL,
      FamilyGUID uniqueidentifier NULL,
      HasBulkLoggedData bit NULL,
      IsSnapshot bit NULL,
      IsReadOnly bit NULL,
      IsSingleUser bit NULL,
      HasBackupChecksums bit NULL,
      IsDamaged bit NULL,
      BeginsLogChain bit NULL,
      HasIncompleteMetaData bit NULL,
      IsForceOffline bit NULL,
      IsCopyOnly bit NULL,
      FirstRecoveryForkID uniqueidentifier NULL,
      ForkPointLSN decimal(25, 0) NULL,
      RecoveryModel nvarchar(60) NULL,
      DifferentialBaseLSN decimal(25, 0) NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      BackupTypeDescription  nvarchar(60) NULL,
      BackupSetGUID uniqueidentifier NULL,
      CompressedBackupSize binary(8) NULL
);

DECLARE @FileList TABLE
      (
      LogicalName nvarchar(128) NOT NULL,
      PhysicalName nvarchar(260) NOT NULL,
      [Type] char(1) NOT NULL,
      FileGroupName nvarchar(120) NULL,
      Size numeric(20, 0) NOT NULL,
      MaxSize numeric(20, 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25,0) NULL,
      DropLSN numeric(25,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25,0) NULL ,
      ReadWriteLSN numeric(25,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32) NULL
 );

DECLARE @DataFilePath nvarchar(256) = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\';
DECLARE @LogFilePath nvarchar(256)  = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\';

--Load backup headers (Backup level Information)
DECLARE @RestoreStatement nvarchar(256);
SET @RestoreStatement = N'RESTORE HEADERONLY FROM DISK=N''' + @BackupFile + '''' ;
INSERT INTO @BackupHeaders
      EXEC(@RestoreStatement);

DECLARE iDB CURSOR FOR SELECT DISTINCT DatabaseName FROM @BackupHeaders 
--For each DB
OPEN iDB
FETCH NEXT FROM iDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN 
	IF @DBName IN ('master','msdb','model','tempdb') GOTO NextDB;	

	PRINT '';
	PRINT '';
	PRINT '-- Restoring :' + @DBName ;
	
	DECLARE @DBNewName nvarchar(256) = N'Restored_' + @dbName ;
	PRINT '--- Destination Database Name: ' + @DBNewName;
	
	--Get the file list for this DB
	SET @RestoreStatement = N'RESTORE FILELISTONLY
		 FROM DISK=N''' + @BackupFile + ''' WITH FILE=' + CAST((SELECT TOP 1 Position from @BackupHeaders WHERE DatabaseName = @DBName) as nvarchar(10));;
	DELETE FROM @FileList;
	INSERT INTO @FileList
		EXEC(@RestoreStatement);
	--SELECT * FROM @FileList;
	
	--Get the physical file names
	DECLARE @DataFileName nvarchar(256) = REPLACE((SELECT LTRIM(RTRIM(RIGHT(PhysicalName,CHARINDEX('\',REVERSE(PhysicalName)) - 1))) AS DataFileName FROM @FileList WHERE Type = 'D' ), '.mdf', '');
	DECLARE @LogFileName nvarchar(256)  = REPLACE((SELECT LTRIM(RTRIM(RIGHT(PhysicalName,CHARINDEX('\',REVERSE(PhysicalName)) - 1))) AS DataFileName FROM @FileList WHERE Type = 'L' ), '.ldf', '');
	PRINT '--- Destination DataFileName: ' + @DBNewName;
	PRINT '--- Destination LogFileName: ' + @DBNewName;

	DECLARE @LogicalDataFileName nvarchar(256) = (SELECT LogicalName FROM @FileList WHERE Type = 'D');
	DECLARE @LogicalLogFileName nvarchar(256)  = (SELECT LogicalName FROM @FileList WHERE Type = 'L');
	PRINT '--- LogicalDestination DataFileName: ' + @DBNewName;
	PRINT '--- LogicalDestination LogFileName: ' + @DBNewName;

	
	DECLARE iBackup CURSOR FOR SELECT Position FROM @BackupHeaders WHERE DatabaseName = @DBName ORDER BY Position ASC 
	--For each backup, in sequence
	OPEN iBackup
	FETCH NEXT FROM iBackup INTO @FileID
	WHILE @@FETCH_STATUS = 0
	BEGIN 
		
		--Restore the file
		PRINT '---- Restoring FileID: ' + CONVERT(nvarchar(5),@FileID)
			
		DECLARE @RestoreSQL nvarchar(max)= N'
		RESTORE DATABASE 
			[' + @dbNewName + ']
			FROM  DISK = N''' + @BackupFile + '''
			WITH  FILE = ' + CONVERT(nvarchar(5),@FileID) + ',
			MOVE N''' + @LogicalDataFileName + ''' TO N''' + @DataFilePath + @DataFileName + ''',
			MOVE N''' + @LogicalLogFileName + ''' TO N''' + @LogFilePath + @LogFileName + ''',
			NORECOVERY,  
			NOUNLOAD,  
			REPLACE'
		--PRINT @RestoreSQL

		EXEC(@RestoreSQL)
		
		PRINT '';
	NextFile:
	FETCH NEXT FROM iBackup INTO @FileID
	END 
	CLOSE iBackup
	DEALLOCATE iBackup
	
	--Recover the database
	PRINT '--- Recovering ' + @DBNewName
	RESTORE Database @DBNewName WITH RECOVERY
	

NextDB:
FETCH NEXT FROM iDB INTO @DBName
END 
CLOSE iDB
DEALLOCATE iDB

 

Note:

  • This was not engineered to handle incrementals, but I believe it should be easily accomidated with a couple small changes (if any)
  • This excludes the system databases via the 'IF @DBName IN...' statement.

Tags:

Tech Tips

Comments are closed

About Me

Joe Havelick is a reasonable facsimile of this photo.


profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites

Recommendations