SQL Server Differential Backups

By: Greg Robidoux

Overview

Another option to assist with your recovery is to create "Differential" backups.  A "Differential" backup is a backup of any extent that has changed since the last "Full" backup was created.

Explanation

The way differential backups work is that they will backup all extents that have changed since the last full backup.  An extent is made up of eight 8KB pages, so an extent is 64KB of data.  Each time any data has been changed a flag is turned on to let SQL Server know that if a "Differential" backup is created it should include the data from this extent.  When a "Full" backup is taken these flags are turned off.

So if you do a full backup and then do a differential backup, the differential backup will contain only the extents that have changed.  If you wait some time and do another differential backup, this new differential backup will contain all extents that have changed since the last full backup.  Each time you create a new differential backup it will contain every extent changed since the last full backup.  When you go to restore your database, to get to the most current time you only need to restore the full backup and the most recent differential backup.  All of the other differential backups can be ignored.

If your database is in the Simple recovery model, you can still use full and differential backups. This does not allow you to do point in time recovery, but it will allow you to restore your data to a more current point in time then if you only had a full backup.

If your database is in the Full or Bulk-Logged recovery model you can also use differential backups to eliminate the number of transaction logs that will need to be restored.  Since the differential will backup all extents since the last full backup, at restore time you can restore your full backup, your most recent differential backup and then any transaction log backups that were created after the most recent differential backup.  This cuts down on the number of files that need to be restored.

Create SQL Server Differential Backup to one disk file

T-SQL

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL
GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select "Differential" as the backup type
• Select "Disk" as the destination
• Click on "Add..." to add a backup file and type "C:\AdventureWorks.DIF" and click "OK"
• Click "OK" again to create the backup