Day 3 of 31 Days of Disaster Recovery: Determining Files to Restore Database

Day 3 of 31 Days of Disaster Recovery: Determining Files to Restore Database

Welcome back for day 3 of my month-long series on Disaster Recovery. For today’s post, I want to address something that, in my experience, has really flummoxed a lot of people who find themselves unprepared to handle a disaster scenario. Determining exactly which files you should restore.

If you missed any of the earlier posts in the series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database

Test Your Restores

Everyone on your team who might be called upon to perform a restore should be performing regular test runs of restoring the backups. It’s this lack of testing that leads to fumbling around trying to figure out what files to restore. Well, whether you’ve practiced or not, you might like a little help to figure out exactly what files are required to restore the database to its most current point. Thus I give you my RestoreScripter script. The script traverses the backup information in the backup tracking tables in the msdb database.

The script automatically handles many of the possible pitfalls that you might experience and gives you the details you need to quickly script out the restores. For example, records with the same “RestoreOrder” are multiple files for the same backup and should be scripted together in a single restore command.

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