Thursday, September 24, 2015

Backup Restore Chain (tested in SQL 2008R2)

This is based on the script created by SQLSoldier (Day 3 of 31 Days of Disaster Recovery: Determining Files to Restore Database) but with slight modifications as it did not correctly display the order to restore in for SQL2008R2.

Comments welcome!  Good or bad.

If you test this on different versions, it would be great if you would comment which versions this works on.

Lines with my changes noted by --Anders


/****************
Created by @SQLSoldier
http://www.sqlsoldier.com/wp/sqlserver/day3of31daysofdisasterrecoverydeterminingfilestorestoredatabase

Modified by Anders Pedersen @arrowdrive
Made to work with SQL 2008R2.

2015-09-24
*****************/
DECLARE @DBName SYSNAME,
   
@DBBackupLSN numeric(250);

DECLARE @Baks TABLE (
   
BakID INT IDENTITY(11) NOT NULL PRIMARY KEY,
   
backup_set_id INT NOT NULL,
   
media_set_id INT NOT NULL,
   
first_family_number tinyint NOT NULL,
   
last_family_number tinyint NOT NULL,
   
first_lsn numeric(250) NULL,
   
last_lsn numeric(250) NULL,
   
database_backup_lsn numeric(250) NULL,
   
backup_finish_date DATETIME NULL,
   
TYPE CHAR(1) NULL,
   
family_sequence_number tinyint NOT NULL,
   
physical_device_name NVARCHAR(260) NOT NULL,
   
device_type tinyint NULL,
   
checkpoint_LSN numeric (25,0)-- Anders
   
)SET NOCOUNT ON;
-- Set the name of the database you want to restoreSET @DBName N'AndersTest';
-- Get the most recent full backup with all backup filesINSERT INTO @Baks (backup_set_id,
   
media_set_id,
   
first_family_number,
   
last_family_number,
   
first_lsn,
   
last_lsn,
   
database_backup_lsn,
   
backup_finish_date,
   
TYPE,
   
family_sequence_number,
   
physical_device_name,
   
device_type
   
,checkpoint_LSN)-- AndersSELECT TOP(1WITH Ties B.backup_set_id,
   
B.media_set_id,
   
B.first_family_number,
   
B.last_family_number,
   
B.first_lsn,
   
B.last_lsn,
   
B.database_backup_lsn,
   
B.backup_finish_date,
   
B.TYPE,
   
BF.family_sequence_number,
   
BF.physical_device_name,
   
BF.device_type
   
B.checkpoint_lsn -- AndersFROM msdb.dbo.backupset AS BINNER JOIN msdb.dbo.backupmediafamily AS BF
   
ON BF.media_set_id B.media_set_id
       
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_numberWHERE B.database_name @DBNameAND B.is_copy_only 0AND B.TYPE = 'D'AND BF.physical_device_name NOT IN ('Nul''Nul:')ORDER BY backup_finish_date DESCbackup_set_id;
-- Get the lsn that the differential backups, if any, will be based onSELECT @DBBackupLSN checkpoint_LSN -- AndersFROM @Baks;
-- Get the most recent differential backup based on that full backupINSERT INTO @Baks (backup_set_id,
   
media_set_id,
   
first_family_number,
   
last_family_number,
   
first_lsn,
   
last_lsn,
   
database_backup_lsn,
   
backup_finish_date,
   
TYPE,
   
family_sequence_number,
   
physical_device_name,
   
device_type)SELECT TOP(1WITH Ties B.backup_set_id,
   
B.media_set_id,
   
B.first_family_number,
   
B.last_family_number,
   
B.first_lsn,
   
B.last_lsn,
   
B.database_backup_lsn,
   
B.backup_finish_date,
   
B.TYPE,
   
BF.family_sequence_number,
   
BF.physical_device_name,
   
BF.device_typeFROM msdb.dbo.backupset AS BINNER JOIN msdb.dbo.backupmediafamily AS BF
   
ON BF.media_set_id B.media_set_id
       
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_numberWHERE B.database_name @DBNameAND B.is_copy_only 0AND B.TYPE = 'I'AND BF.physical_device_name NOT IN ('Nul''Nul:')
AND 
B.database_backup_lsn @DBBackupLSN
ORDER BY backup_finish_date DESCbackup_set_id;

--select * from @Baks

-- Get the last LSN included in the differential backup,
-- if one was found, or of the full backup
SELECT TOP @DBBackupLSN last_lsnFROM @BaksWHERE TYPE IN ('D''I')ORDER BY BakID DESC;
-- Get first log backup, if any, for restore, where
-- last_lsn of previous backup is >= first_lsn of the
-- log backup and <= the last_lsn of the log backup
INSERT INTO @Baks (backup_set_id,
   
media_set_id,
   
first_family_number,
   
last_family_number,
   
first_lsn,
   
last_lsn,
   
database_backup_lsn,
   
backup_finish_date,
   
TYPE,
   
family_sequence_number,
   
physical_device_name,
   
device_type)SELECT TOP(1WITH Ties B.backup_set_id,
   
B.media_set_id,
   
B.first_family_number,
   
B.last_family_number,
   
B.first_lsn,
   
B.last_lsn,
   
B.database_backup_lsn,
   
B.backup_finish_date,
   
B.TYPE,
   
BF.family_sequence_number,
   
BF.physical_device_name,
   
BF.device_type FROM msdb.dbo.backupset BINNER JOIN msdb.dbo.backupmediafamily AS BF
   
ON BF.media_set_id B.media_set_id
       
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_numberWHERE B.database_name @DBNameAND B.is_copy_only 0AND B.TYPE = 'L'AND BF.physical_device_name NOT IN ('Nul''Nul:')
AND 
@DBBackupLSN BETWEEN B.first_lsn AND B.last_lsnORDER BY backup_finish_datebackup_set_id;
-- Get last_lsn of the first log backup that will be restoredSET @DBBackupLSN NULL;SELECT @DBBackupLSN MAX(last_lsn)FROM @BaksWHERE TYPE = 'L';
-- Recursively get all log backups, in order, to be restored
-- first_lsn of the log backup = last_lsn of the previous log backup
WITH LogsAS (SELECT B.backup_set_id,
       
B.media_set_id,
       
B.first_family_number,
       
B.last_family_number,
       
B.first_lsn,
       
B.last_lsn,
       
B.database_backup_lsn,
       
B.backup_finish_date,
       
B.TYPE,
       
BF.family_sequence_number,
       
BF.physical_device_name,
       
BF.device_type,
       
AS LogLevel
   
FROM msdb.dbo.backupset B
   
INNER JOIN msdb.dbo.backupmediafamily AS BF
       
ON BF.media_set_id B.media_set_id
           
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
   
WHERE B.database_name @DBName
   
AND B.is_copy_only 0
   
AND B.TYPE = 'L'
   
AND BF.physical_device_name NOT IN ('Nul''Nul:')
   AND 
B.first_lsn @DBBackupLSN
   
UNION ALL
   
SELECT B.backup_set_id,
       
B.media_set_id,
       
B.first_family_number,
       
B.last_family_number,
       
B.first_lsn,
       
B.last_lsn,
       
B.database_backup_lsn,
       
B.backup_finish_date,
       
B.TYPE,
       
BF.family_sequence_number,
       
BF.physical_device_name,
       
BF.device_type,
       
L.LogLevel 1
   
FROM msdb.dbo.backupset B
   
INNER JOIN msdb.dbo.backupmediafamily AS BF
       
ON BF.media_set_id B.media_set_id
           
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
   
INNER JOIN Logs L ON L.database_backup_lsn B.database_backup_lsn
   
WHERE B.database_name @DBName
   
AND B.is_copy_only 0
   
AND B.TYPE = 'L'
   
AND BF.physical_device_name NOT IN ('Nul''Nul:')
   AND 
B.first_lsn L.last_lsn)INSERT INTO @Baks (backup_set_id,
   
media_set_id,
   
first_family_number,
   
last_family_number,
   
first_lsn,
   
last_lsn,
   
database_backup_lsn,
   
backup_finish_date,
   
TYPE,
   
family_sequence_number,
   
physical_device_name,
   
device_type)SELECT backup_set_id,
   
media_set_id,
   
first_family_number,
   
last_family_number,
   
first_lsn,
   
last_lsn,
   
database_backup_lsn,
   
backup_finish_date,
   
TYPE,
   
family_sequence_number,
   
physical_device_name,
   
device_typeFROM LogsOPTION(MaxRecursion 0);
-- Select out just the columns needed to script restoreSELECT RestoreOrder Row_Number() OVER(Partition BY family_sequence_number ORDER BY BakID),
   
RestoreType CASE WHEN TYPE IN ('D''I'THEN 'Database'
           
WHEN TYPE = 'L' THEN 'Log'
       
END,
   
DeviceType CASE WHEN device_type IN (2102THEN 'Disk'
           
WHEN device_type IN (5105THEN 'Tape'
       
END,
   
PhysicalFileName physical_device_nameFROM @BaksORDER BY BakID;

SET NOCOUNT OFF;



Result will look something like this: