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(25, 0);

DECLARE @Baks TABLE (
   BakID INT IDENTITY(1, 1) 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(25, 0) NULL,
   last_lsn numeric(25, 0) NULL,
   database_backup_lsn numeric(25, 0) 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(1) WITH 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 DESC, backup_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(1) WITH 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 DESC, backup_set_id;

--select * from @Baks

-- Get the last LSN included in the differential backup,
-- if one was found, or of the full backupSELECT TOP 1 @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 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(1) WITH 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_date, backup_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 backupWITH 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,
       1 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 (2, 102) THEN 'Disk'
           WHEN device_type IN (5, 105) THEN 'Tape'
       END,
   PhysicalFileName = physical_device_nameFROM @BaksORDER BY BakID;

SET NOCOUNT OFF;

Result will look something like this: