Differential Database Backups

A differential database backup records only the data that has changed since the last full database backup. This full backup is called the differential base. Differential database backups are smaller and faster than full database backups. This saves backup time at the cost of increased complexity. For large databases, differential backups can occur at shorter intervals than database backups. This reduces the work-loss exposure.

Differential database backups are especially useful if a subset of a database is modified more frequently than the rest of the database. In these cases, differential database backups enable you back up frequently without the overhead of full database backups.

Best Practices for Scheduling Differential Database Backups

For large databases, a full database backup requires significant disk space. To save time and disk space, you can follow a full database backup with a series of differential backups. Each successive differential database backup is larger than its predecessor, requiring more backup time, space, and restore time. Therefore, we recommend that you take a new full backup at an appropriate interval to provide a new differential base.

As a best practice, when you use differential database backups, we recommend the following guidelines:

1. After a full database backup, schedule differential database backups periodically. For example, you might take a differential database backup every four hours or, for highly active systems, even more frequently.
2. At an interval that makes sure that your differential backups do not become too large, schedule a new full database backup. For example, you might back up the full database one time per week.

Differential Database Backups Strategy (Simple Recovery Model)

Under the simple recovery model, work-loss exposure between backups increases over time. The following illustration shows a backup strategy that reduces work-loss exposure by using database backups supplemented by differential database backups. After the first database backup, a series of three differential backups is taken. The third differential backup is large enough that the next backup is a database backup. This database backup establishes a new differential base.

Differential Database Backups Strategy (Full Recovery Model)

With the full and bulk-logged recovery models, differential database backups minimize the time that is required to roll forward transaction log backups when restoring a database. A differential database backup restores the database to when the differential backup was completed.

The following illustration shows a backup strategy that supplements full database backups with differential database backups, and also shows a series of routine log backups. The presence of transaction log backups reduces potential work-loss exposure to the time after the most recent log backup. After the first database backup, a series of three differential backups is taken. The third differential backup is large enough that the next backup is a full database backup. This full database backup establishes a new differential base.

Before the first database backup in this illustration, the database is exposed to potential work loss (from time t0 to time t1). Thereafter, routine log backups reduce work-loss exposure to the risk of losing changes that were made after the latest log backup (taken at time t14). If a disk failure occurs, the database administrator should immediately try to back up the active log (the tail of the log). If this tail-log backup succeeds, the database can be restored up to the point of failure.

Creating and Scheduling Differential Database Backups

The required BACKUP syntax for creating a differential database backup is:

BACKUP DATABASE database_name TO <backup_device> WITH DIFFERENTIAL

To create a differential database backup

• How to: Create a Differential Database Backup (Transact-SQL)
• How to: Create a Differential Database Backup (SQL Server Management Studio)
• SqlBackup (SMO)

To schedule backup jobs

• Maintenance Plan Wizard

Using a Differential Database Backup to Restore the Database

Before you restore a differential backup, you must restore its base. If you take a series of full differentials for a specified base, you only have to restore the base and the most recent differential at restore time. For more information, see Performing a Complete Database Restore (Simple Recovery Model) or Performing a Complete Database Restore (Full Recovery Model).

See Also

Concepts

Backing Up Read-Only Databases
Full Database Backups
Performing a Complete Database Restore (Full Recovery Model)
Performing a Complete Database Restore (Simple Recovery Model)
Reducing Recovery Time When Restoring a Database
Working with Transaction Log Backups

Other Resources

Using Differential Backups

Help and Information

Getting SQL Server 2005 Assistance

Change History