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
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
- When are Checksums Written to a Page
- How to CHECKDB Like a Boss
- How Much Log Can a Backup Log
- The Case of the Backups That Wouldn’t Restore
- Who Deleted That Data?
- 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:TestDiffData'; Exec xp_create_subdir 'd:TestDiffLogs'; Go -- Create new database in unique location Create Database TestDiff On (Name = N'TestDiff', FileName = N'd:TestDiffDataTestDiff.mdf') Log On (Name = N'TestDiff_log', FileName = N'd:TestDiffLogsTestDiff_log.ldf'); Go -- Take Full Backup Backup Database TestDiff To Disk = 'd:BackupTestDiffDay1.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:TestDiffDataTestDiff' + Cast(@Cntr as nvarchar(200)) + '.ndf'');'; Exec sp_executesql @SQL; Set @SQL = N'Backup Database TestDiff To Disk = ''d:BackupTestDiffDay' + 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:TestDiff2Data'; Exec xp_create_subdir 'd:TestDiff2Logs'; Go -- Restore backup moving files to new location Restore Database TestDiff2 From Disk = 'd:BackupTestDiffDay1.bak' With Move 'TestDiff' To 'd:TestDiff2DataTestDiff.mdf', Move 'TestDiff_log' To 'd:TestDiff2LogsTestDiff_log.ldf', NoRecovery; Go -- Restore file list of most recent differential backup Restore FileListOnly From Disk = 'd:BackupTestDiffDay6_Diff.bak'; Go -- 6 new database files that need to be accounted for: -- TestDiff1 d:TestDiffDataTestDiff1.ndf -- TestDiff2 d:TestDiffDataTestDiff2.ndf -- TestDiff3 d:TestDiffDataTestDiff3.ndf -- TestDiff4 d:TestDiffDataTestDiff4.ndf -- TestDiff5 d:TestDiffDataTestDiff5.ndf -- TestDiff6 d:TestDiffDataTestDiff6.ndf -- Restore differential backup moving files Restore Database TestDiff2 From Disk = 'd:BackupTestDiffDay6_Diff.bak' With Move 'TestDiff1' TO 'd:TestDiff2DataTestDiff1.ndf', Move 'TestDiff2' TO 'd:TestDiff2DataTestDiff2.ndf', Move 'TestDiff3' TO 'd:TestDiff2DataTestDiff3.ndf', Move 'TestDiff4' TO 'd:TestDiff2DataTestDiff4.ndf', Move 'TestDiff5' TO 'd:TestDiff2DataTestDiff5.ndf', Move 'TestDiff6' TO 'd:TestDiff2DataTestDiff6.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:BackupTestDiffDay6_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:TestDiff2Data'; Set @MoveLogFilesTo = N'd:TestDiff2Logs'; Set @MoveFilestreamTo = N'd:TestDiff2FS'; Set @MoveFTCatalogTo = N'd:TestDiff2FT'; -- 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)
Day 24 of 31 Days of Disaster Recovery: Handling Corruption in a Clustered Index | SQLSoldier
[…] Restoring Differential Backups With New Files […]
Day 26 of 31 Days of Disaster Recovery: The Mysterious Case of the Long Backup | SQLSoldier
[…] Restoring Differential Backups With New Files […]
Amit Porwal
We have taken 1 Full backup and 4 ( 1, 2 3, 4 ) Differential Backup, so which differential backup should be taken for recovery.
SQLSoldier
You mean to restore the database? If you want to restore it to the most recent point possible, then restore the full backup with norecovery followed by the most recent differential (#4). If you have log files to recover too, be sure to use norecovery for the restore of the differential file and then restore the log backups created since the differential was created.