SQL Server Differential Backup Tutorial

What is a Differential Backup?

A differential backup records only the data that has changed since the last full backup. It does not include unchanged data, which reduces the backup size and time.

Unlike transaction log backups that capture all transaction log activity, differential backups focus on data pages modified since the last full backup.

• Depends on a base full backup.
• Contains all changes since the last full backup.
• Faster than full backups but larger than log backups.
• Useful for reducing backup windows.

How Differential Backups Work

When you perform a full backup, SQL Server marks the database pages as the baseline.

Each time data changes, SQL Server tracks which pages have been modified.

A differential backup copies all pages changed since the last full backup, regardless of any previous differential backups.

• Differential backups grow larger over time until the next full backup.
• Restoring requires the last full backup plus the latest differential backup.

Creating a Differential Backup

You can create a differential backup using SQL Server Management Studio (SSMS) or T-SQL commands.

The key is to specify the differential option when backing up.

Restoring a Differential Backup

To restore a differential backup, you must first restore the last full backup with NORECOVERY, then restore the differential backup with RECOVERY.

This process ensures the database is restored to the point of the differential backup.

• Restore full backup with NORECOVERY option.
• Restore differential backup with RECOVERY option.

Example: Creating and Restoring Differential Backup

Here is an example of creating and restoring a differential backup using T-SQL.