Delen via

Differential Database Backups

  • Artikel

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.

Note

Differential backups of a read/write database operate correctly even if one of the filegroups was read-only when the differential base was created. As with any differential backup of a read/write database, the Database Engine records the differential base in the primary file.

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.

Note

Under the full recovery model, you must also schedule transaction log backups. We recommend that you take log backups more frequently than differential database backups. For more information, see Working with Transaction Log Backups.

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.

Full and differential database backups

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.

Full & differential database backups & log backups

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.

Note

The transaction log backups let you recover to the point of failure. For more information, see Restoring a Database to a Point Within a Backup.

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

Note

For more information about how to use differential backups, including best practices, see Using Differential Backups.

To schedule backup jobs

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).

Important

We recommend that you keep all the differential backups that you create for as long as you keep the base. If the most recent differential backup is damaged, you can use a previous differential to restore the database.

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

Release History

17 July 2006
New content:
  • Added summary of the basic BACKUP syntax for creating a differential database backup.
Changed content:
  • Removed incorrect note about full differential backup operation.