Day 23 of 31 Days of Disaster Recovery: Restoring Differential Backups With New Files

It’s day 23 of my 31 Days of Disaster Recovery series, and today’s blog post is inspired from an email i received in response to day 20′s blog post The Case of the Backups That Wouldn’t Restore.

A friend shared a story with me via email about a partial restore scenario that was very complex. These are the complexities he described:

• Weekly full backups and daily differential backups
• New data files are added daily for their partitioned tables
• 1508 total files in the database
• Restore server had a different drive layout, and all files had to be moved to different locations

Additionally, he was performing partial restore of only specific filegroups, so his restore process was even more difficult than what I am going to cover today. Needless to say, he had a lot of move commands to write. The GUI in SQL Server 2012 has an option to move all files of a specific type to the same location, but we’re not going to cover the restore GUI in this blog (ever). This brought back memories of past consultations where people needed to restore a differential file where a new file had been added and the location wasn’t available on the restore server. So in today’s post is about how to restore a differential backup when files have been added.

If you missed any of the earlier posts in my DR 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
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors
11. Converting LSN Formats
12. Extreme Disaster Recovery Training
13. Standard Backup Scripts
14. Fixing a Corrupt Tempdb
15. Running DBCC CheckTable in Parallel Jobs
16. Disaster Recovery Gems From Around The Net
17. When are Checksums Written to a Page
18. How to CHECKDB Like a Boss
19. How Much Log Can a Backup Log
20. The Case of the Backups That Wouldn’t Restore
21. Who Deleted That Data?
22. Which DBCC CHECK Commands Update Last Known Good DBCC

Restore Demo

I worked up a demo that you can walk through to see exactly how you would handle new files being added to a database between differential backups. In this scenario, we’re going to simulate a week of differential backups and add a new file each day before the differential backup. Then we are going to restore the full backup and the most recent differential backup to a new location using MOVE arguments.

Download the scripts in a zip file: DifferentialRestoresWithMove.zip (2 KB)

-- Create unique locations for database files
Exec xp_create_subdir 'd:\TestDiff\Data';
Exec xp_create_subdir 'd:\TestDiff\Logs';
Go

-- Create new database in unique location
Create Database TestDiff
    On (Name = N'TestDiff',
        FileName = N'd:\TestDiff\Data\TestDiff.mdf')
    Log On (Name = N'TestDiff_log',
        FileName = N'd:\TestDiff\Logs\TestDiff_log.ldf');
Go

-- Take Full Backup
Backup Database TestDiff
    To Disk = 'd:\Backup\TestDiffDay1.bak'
    With Init;
Go

-- Add file for 6 (simulated) days and perform a differential backup
Declare @Cntr int = 1,
    @SQL nvarchar(200);

While @Cntr <= 6
  Begin
    Set @SQL = N'Alter Database TestDiff
                Add File (Name = ''TestDiff' +
                        Cast(@Cntr as nvarchar(200)) + ''',
                    FileName = ''d:\TestDiff\Data\TestDiff' +
                        Cast(@Cntr as nvarchar(200)) + '.ndf'');';

    Exec sp_executesql @SQL;

    Set @SQL = N'Backup Database TestDiff
                To Disk = ''d:\Backup\TestDiffDay' +
                    Cast(@Cntr as nvarchar(200)) + '_diff.bak''
                With Differential, Init;';

    Exec sp_executesql @SQL;

    Set @Cntr = @Cntr + 1;
  End
Go

-- Restore the full backup as TestDiff2 to new location
-- Create unique locations for database files
Exec xp_create_subdir 'd:\TestDiff2\Data';
Exec xp_create_subdir 'd:\TestDiff2\Logs';
Go

-- Restore backup moving files to new location
Restore Database TestDiff2
    From Disk = 'd:\Backup\TestDiffDay1.bak'
    With Move 'TestDiff' To 'd:\TestDiff2\Data\TestDiff.mdf',
        Move 'TestDiff_log' To 'd:\TestDiff2\Logs\TestDiff_log.ldf',
        NoRecovery;
Go

-- Restore file list of most recent differential backup
Restore FileListOnly
    From Disk = 'd:\Backup\TestDiffDay6_Diff.bak';
Go

-- 6 new database files that need to be accounted for:
-- TestDiff1    d:\TestDiff\Data\TestDiff1.ndf
-- TestDiff2    d:\TestDiff\Data\TestDiff2.ndf
-- TestDiff3    d:\TestDiff\Data\TestDiff3.ndf
-- TestDiff4    d:\TestDiff\Data\TestDiff4.ndf
-- TestDiff5    d:\TestDiff\Data\TestDiff5.ndf
-- TestDiff6    d:\TestDiff\Data\TestDiff6.ndf
-- Restore differential backup moving files
Restore Database TestDiff2
    From Disk = 'd:\Backup\TestDiffDay6_Diff.bak'
    With Move 'TestDiff1' TO 'd:\TestDiff2\Data\TestDiff1.ndf',
        Move 'TestDiff2' TO 'd:\TestDiff2\Data\TestDiff2.ndf',
        Move 'TestDiff3' TO 'd:\TestDiff2\Data\TestDiff3.ndf',
        Move 'TestDiff4' TO 'd:\TestDiff2\Data\TestDiff4.ndf',
        Move 'TestDiff5' TO 'd:\TestDiff2\Data\TestDiff5.ndf',
        Move 'TestDiff6' TO 'd:\TestDiff2\Data\TestDiff6.ndf',
        Recovery;
Go

i also worked up a quick script to generate the MOVE commands. This could come in handy if you ever need to generate the MOVE command for a backup with a large number of files that need to be moved. This is just a quick script, not a well-evolved one, so you will need to take the output and remove the comma from the final line if you use it.

Declare @BackupFile nvarchar(500),
    @FileNumberInBackup int,
    @MoveDataFilesTo nvarchar(500),
    @MoveLogFilesTo nvarchar(500),
    @MoveFilestreamTo nvarchar(500),
    @MoveFTCatalogTo nvarchar(500),
    @RestoreCmd nvarchar(max)
Declare @FileList Table (LogicalName nvarchar(128),
    PhysicalName nvarchar(260),
    Type char(1),
    FileGroupName nvarchar(128),
    Size numeric(20,0),
    MaxSize numeric(20,0),
    FileID bigint,
    CreateLSN numeric(25,0),
    DropLSN numeric(25,0) NULL,
    UniqueID uniqueidentifier,
    ReadOnlyLSN numeric(25,0) NULL,
    ReadWriteLSN numeric(25,0) NULL,
    BackupSizeInBytes bigint,
    SourceBlockSize int,
    FileGroupID int,
    LogGroupGUID uniqueidentifier NULL,
    DifferentialBaseLSN numeric(25,0) NULL,
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnly bit,
    IsPresent bit,
    TDEThumbprint varbinary(32))

-- Define backup file path/name
Set @BackupFile = N'd:\Backup\TestDiffDay6_Diff.bak';
-- Define file number of file in backup (default 1)
Set @FileNumberInBackup = 1;
-- Define destination path (not name) for all file types
Set @MoveDataFilesTo = N'd:\TestDiff2\Data';
Set @MoveLogFilesTo = N'd:\TestDiff2\Logs';
Set @MoveFilestreamTo = N'd:\TestDiff2\FS';
Set @MoveFTCatalogTo = N'd:\TestDiff2\FT';

-- Add trailing slash if not exists
If Right(@MoveDataFilesTo, 1) <> '\'
    Set @MoveDataFilesTo = @MoveDataFilesTo + '\';

If Right(@MoveLogFilesTo, 1) <> '\'
    Set @MoveLogFilesTo = @MoveLogFilesTo + '\';

-- Restore file list of most recent differential backup
Insert Into @FileList
Exec sp_executesql N'Restore FileListOnly
        From Disk = @BackupFile
        With File = @FileNumberInBackup;',
    N'@BackupFile nvarchar(500), @FileNumberInBackup int',
    @BackupFile = @BackupFile,
    @FileNumberInBackup = @FileNumberInBackup;

Select MoveCmd = 'Move ''' + LogicalName + ''' To ''' +
    Case Type When 'D' Then @MoveDataFilesTo +
            Right(PhysicalName,
                CharIndex('\', Reverse(PhysicalName)) - 1)
        When 'L' Then @MoveLogFilesTo + 
            Right(PhysicalName,
                CharIndex('\', Reverse(PhysicalName)) - 1)
        When 'F' Then @MoveFilestreamTo
        When 'S' Then @MoveFTCatalogTo
        Else PhysicalName
    End + ','
From @FileList
Where IsPresent = 1
Order By FileID;
Go

Summary

It’s actually not very difficult to deal with files being added in a differential (or log) backup when you need to move the locations. You simply need to identify the files using RESTORE FILELISTONLY and then use the MOVE argument to define the new location. But it can be tedious when there are a large number of files involved. That’s where scripts like these come in handy. Enjoy.

Download the scripts in a zip file: DifferentialRestoresWithMove.zip (2 KB)