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.