Working with Multibase Differential Backups

  • Article

While working with databases, SQL Server Database Administrators are required to take various types of backups to ensure minimum data loss caused by a failure. Backups include full database backups, file and filegroup backups, differential backups and transaction log backups. Most of us tend to append the current date & time in the filenames, so as to be able to identify the sequence of the backups.

While restoring the backups, we know, by the date and time, the restore sequence that needs to be followed. Often, we have seen, that if we try to restore the database backups in an incorrect sequence, the restore fails. Now, the question arises: How does SQL Server know which backup comes after which? Lets have a look:

Note: In the following example we would be working with Multibase differential backups. Multibase differential backups are relevant only for Full and Bulk-Logged recovery models. Under the simple recovery model, a differential backup must have one base. Multibase differential backups are prohibited, and trying to use a multibase differential backup displays an error and fails.

Let us create a database with two file groups, the Primary filegroup having one datafile and the Secondary filegroup having two files:

CREATE

DATABASE [Test4Backup] ON PRIMARY
( NAME = N'Test4Backup_Primary_File1', FILENAME = N'C:\Test4Backup_Primary_File1.mdf' ),
FILEGROUP [SECONDARY]
( NAME = N'Test4Backup_Secondary_File1', FILENAME = N'C:\Test4Backup_Secondary_File1.ndf' ),
( NAME = N'Test4Backup_Secondary_File2', FILENAME = N'C:\Test4Backup_Secondary_File2.ndf' )
LOG ON
( NAME = N'Test4Backup_log', FILENAME = N'C:\Test4Backup_log.ldf' )
GO

Lets now, take a sequence of backups. The sequence that we would be following is:

  1. A full database backup. A fullbackup is required as the starting point of the backup set. In the absence of a full backup, differential backup attempts fail.
  2. A database differential backup.
  3. A filegroup backup of the secondary filgroup.
  4. A file backup of the file in the primary filegroup.
  5. A file backup of the second file in the secondary filegroup.
  6. A database differential backup.

Lets examine each of the backup files to understand what should be the restore order. We will run the following commands against each of the backup files:

RESTORE HEADERONLY FROM DISK = 'Full path to backup file'
RESTORE FILELISTONLY FROM DISK = 'Full path to backup file'

For the full database backup (taken is step 1), we will see the following:

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
----------------- ----------------- ----------------- -----------------
28000000012100045 28000000014600001 28000000012100045 0               

LogicalName DifferentialBaseLSN
--------------------------- -------------------
Test4Backup_Primary_File1 0
Test4Backup_Secondary_File1 0
Test4Backup_Secondary_File2 0

So, the first LSN for the full backup is 28000000012100045, which becomes the DifferentialBaseLSN for subsequent differential backups. For the differential backup (taken is step 2), we see the following information:

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
----------------- ----------------- ----------------- -----------------
28000000015600034 28000000017100001 28000000015600034 28000000012100045

LogicalName DifferentialBaseLSN
--------------------------- -------------------
Test4Backup_Primary_File1 28000000012100045
Test4Backup_Secondary_File1 28000000012100045
Test4Backup_Secondary_File2 28000000012100045

This means that to be able to apply this differential backup, we need to apply a full backup that has 28000000012100045 as its first LSN. Now, lets examine the filegroup backup taken in step 3:

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
----------------- ----------------- ----------------- -----------------
28000000017700067 28000000020600001 28000000017700067 28000000012100045

LogicalName DifferentialBaseLSN
--------------------------- -------------------
Test4Backup_Primary_File1 28000000012100045
Test4Backup_Secondary_File1 28000000012100045
Test4Backup_Secondary_File2 28000000012100045

Again, the DifferentialBaseLSN of the files is 28000000012100045, meaning that to be able to apply this filegroup backup, we need to apply a full backup that has 28000000012100045 as its first LSN. Lets now go on to the file backup in step 4:

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
----------------- ----------------- ----------------- -----------------
28000000021500037 28000000023100001 28000000021500037 28000000012100045

LogicalName DifferentialBaseLSN
--------------------------- -------------------
Test4Backup_Primary_File1 28000000012100045
Test4Backup_Secondary_File1 28000000017700067
Test4Backup_Secondary_File2 28000000017700067

Now, things start becoming more interesting. The DifferentialBaseLSN of the file in the Primary filegroup is still 28000000012100045, while the DifferentialBaseLSN of the files in the secondary filegroups have changed to 28000000017700067. This means that, to be able to apply this backup, we need to apply a backup that has 28000000017700067 as its FirstLSN.

Moving on to the backup taken in step 5:

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
----------------- ----------------- ----------------- -----------------
28000000023800034 28000000025300001 28000000023800034 28000000012100045

LogicalName DifferentialBaseLSN
--------------------------- -------------------
Test4Backup_Primary_File1 28000000021500037
Test4Backup_Secondary_File1 28000000017700067
Test4Backup_Secondary_File2 28000000017700067

Now, the DifferentialBaseLSN of the file in the Primary filegroup is 28000000021500037, while the DifferentialBaseLSN of the files in the secondary filegroups remains unchanged at 28000000017700067. This means that, to be able to apply this backup, we need to apply a backup that has 28000000021500037 as its FirstLSN. Moving on to the last step, we have a backup file with the following properties:

FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN
----------------- ----------------- ----------------- -----------------
28000000025500035 28000000027200001 28000000025500035 28000000012100045

LogicalName DifferentialBaseLSN
--------------------------- -------------------
Test4Backup_Primary_File1 28000000021500037
Test4Backup_Secondary_File1 28000000017700067
Test4Backup_Secondary_File2 28000000023800034

Now, we see that the DifferentialBaseLSN of the last file has changed to 28000000023800034. Again, this means that, to be able to apply this backup, we need to apply a backup with FirstLSN 28000000023800034.

The million dollar question now is, how do we know what should be the restore order of the backups. The rule of thumb is, first restore the full backup. Now, after each backup, look at the FirstLSN of the restored backup and the DifferentialBaseLSN of the backups sets yet to be restored. The backup set that has at least one DifferentialBaseLSN equal to the FirstLSN of the currently restored backup and all other DifferentialBaseLSNs either equal to or less than the FirstLSN of the currently restored backup comes next.

In our example, we start off by applying the full backup. Now, the FirstLSN of the currently restored backup is 28000000012100045, hence we should be looking for a backup set that has at least one instance of 28000000012100045 as the DifferentialBaseLSN and all other DifferentialBaseLSNs either equal to or less than 28000000012100045. Now, both the differential and the filegroup backup of the secondary filegroup are eligible. By looking at the BackupType of the differential backup, we know it is a differential backup, and hence can be omitted. Hence, our obvious choice is the filegroup backup.

Once this is applied, the FirstLSN of the currently restored backup becomes 28000000017700067. Now, we should look for a backup that has at least one DifferentialBaseLSN as 28000000017700067 and all other either equal to or less than 28000000017700067. The choice now is the backup that has FirstLSN 28000000021500037. We should continue in a similar fashion, till we reach a stage when we are left with only differential backups that need to be applied. At this point, the differential backup that has the maximum FirstLSN should be applied.

Recommended readings: Backing Up and Restoring Databases in SQL Server from Books OnLine.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.