Differential Backups

This document provides a comprehensive guide to implementing and managing differential backups in SQL Server. Differential backups capture only the data that has changed since the last full backup, offering a more efficient backup strategy compared to taking full backups repeatedly.

Key Concept: A differential backup is based on the most recent full backup. Subsequent differential backups are based on the previous differential backup. This significantly reduces backup time and storage space.

When to Use Differential Backups

Differential backups are ideal for scenarios where you need a balance between the speed of log backups and the simplicity of full backups. They are particularly useful for:

Reducing backup time and storage requirements between full backups.
Simplifying the restore process compared to managing multiple transaction log backups.
Maintaining a consistent recovery point objective (RPO) without the overhead of frequent full backups.

How Differential Backups Work

When a differential backup is created, SQL Server marks all data pages that have changed since the last full backup. The differential backup then contains only these modified pages. To restore a database that uses differential backups, you need:

The most recent full backup.
The most recent differential backup taken after the full backup.
All subsequent differential backups (if any), in the order they were taken.

Tip: The frequency of differential backups depends on your RPO and the rate of data change in your database. A common strategy is to take a full backup weekly and differential backups daily.

Creating a Differential Backup

Using Transact-SQL (T-SQL)

You can create a differential backup using the BACKUP DATABASE statement with the DIFFERENTIAL option:

BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backup\YourDatabaseName_Diff.bak'
WITH DIFFERENTIAL, NORECOVERY, STATS = 10;

YourDatabaseName: Replace with the name of your database.
'C:\Backup\YourDatabaseName_Diff.bak': Specify the path and filename for the backup.
WITH DIFFERENTIAL: Indicates that this is a differential backup.
NORECOVERY: Allows further backups to be applied after this one (essential for multi-backup restores).
STATS = 10: Displays progress updates every 10 percent.

Using SQL Server Management Studio (SSMS)

In Object Explorer, connect to the SQL Server Database Engine instance.
Expand the Databases node.
Right-click the database you want to back up, point to Tasks, and then select Back Up....
In the Back Up Database dialog box:
- Select Full as the Backup type. (Note: While this seems counterintuitive, the option to mark it as differential is later).
- In the Destination section, specify a backup destination.
- Click the Options page.
- In the Differential backup section, select Yes, create a differential backup.
Click OK to create the differential backup.

Restoring from a Differential Backup

To restore a database using a full and differential backup, follow these steps:

Restore the last full backup first.
Restore the last differential backup taken after the full backup.

Example T-SQL restore command:

-- Restore the full backup (using NORECOVERY)
RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\Backup\YourDatabaseName_Full.bak'
WITH NORECOVERY, REPLACE;

-- Restore the differential backup (using NORECOVERY)
RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\Backup\YourDatabaseName_Diff.bak'
WITH NORECOVERY;

-- Bring the database online
RESTORE DATABASE YourDatabaseName WITH RECOVERY;

Considerations for Differential Backups

Differential Base: The performance of differential backups depends on the size of the changes made since the last full backup, not the size of the last differential backup. If many changes have occurred since the full backup, the differential backup file can become large.
Restore Complexity: While simpler than a chain of transaction log backups, restoring requires both the full backup and the most recent differential backup.
Full Backup Frequency: Regularly scheduled full backups are crucial to keep differential backup sizes manageable and the restore process efficient.

Microsoft Learn