A differential backup is created similarly to a full backup, but with one important difference – the differential backup only contains the data that has changed since the last full backup (the active portion of the transaction log). Differential backups are cumulative not incremental. This means that a differential backup contains all changes made since the last full backup, in spite of the fact that they have already been included in previous differential backups. Differential backups are created the following way:
How Differential Backups Work
Every 4GB section (GAM interval) of data file has a special database page that is called differential bitmap. It tracks to which extent (i.e. eight physically contiguous pages) any data has changed since the last full backup. The differential backup reads all bitmaps and looks for the data file extents that are marked as changed in order to backup them.
The size of a differential backup depends directly on the amount of data that has changed. The more changes are marked in the differential bitmaps the bigger next differential backup will be. Ultimately the differential database backup can become as large as the full backup. Therefore, it is recommended to make the next full backup regularly to reset all bitmaps in the differential bitmap, thus zeroizing the differential backup size.
Differential Backup Chain
Every differential backup has its own LSN chain and each full backup resets the differential base LSN. Therefore, to make an extra (unscheduled) full database backup you need to use T-SQL option “WITH COPY_ONLY”, otherwise the differential backup chain will be broken and all subsequent differential backups will follow the new full backup, instead of scheduled full database backups.
BACKUP DATABASE your_database TO DISK = 'full.bak' WITH COPY_ONLY
How to Make a Differential Backup
To make a differential database backup simply add “WITH DIFFERENTIAL” clause:
BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL
Using SQL Server Management Studio (SSMS) is another way of creating differential backups: right click on the database you want to backup, select “Tasks”, then “Back up…”. Choose “Differential” type, add backup destination and click “OK”.
If you prefer third-party utilities you can also use SQLBackupAndFTP to create regular full and differential backups.
