31 Days of Disaster Recovery

The Script

As usual, rather than explain the various steps in the script here, I have included extensive comments inline in the script at each step. You can grab the script below or download it as a zipped file here: RestoreScripter.zip (2 KB)

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)
Set NoCount On;
-- Set the name of the database you want to restore

Set @DBName = N'';

-- Get the most recent full backup with all backup files

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(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 As 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 = '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 on

Select @DBBackupLSN = database_backup_lsn

From @Baks;
-- Get the most recent differential backup based on that full 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(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 As 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 = '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;
-- Get the last LSN included in the differential backup,

-- if one was found, or of the full backup

Select Top 1 @DBBackupLSN = last_lsn

From @Baks

Where 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(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 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 @DBBackupLSN Between B.first_lsn And B.last_lsn

Order By backup_finish_date, backup_set_id;
-- Get last_lsn of the first log backup that will be restored

Set @DBBackupLSN = Null;

Select @DBBackupLSN = Max(last_lsn)

From @Baks

Where 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 Logs

As (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_type

From Logs

Option(MaxRecursion 0);
-- Select out just the columns needed to script restore

Select 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_name

From @Baks

Order By BakID

;
Set NoCount Off;

Sample Output



Sample Restore Files