How Differential Backups Work
New: 5 December 2005
.gif "Icon showing a blue database disk")
This topic is relevant for all types of databases.
A differential backup is based on the most recent, previous full backup. This is known as the base of the differential. A differential backup includes only the data that has changed since the differential base.
The size of a differential backup depends on the amount of data that has changed since the base. Generally, the older a base is, the larger a new differential backup will be. A specific differential backup captures the state of changed extents at that time that backup is created. If you create a series of differential backups, a frequently updated extent is likely to contain different data in each differential. As the differential backups increase in size, restoring a differential backup can significantly increase the time that is required to restore a database. Therefore, we recommend that you take a new full backup at set intervals to establish a new differential base for the data. For example, you might take a weekly full backup of the whole database (that is, a full database backup) followed by a regular series of differential database backups during the week.
The following illustration shows how a differential backup works. The figure shows 24 data extents, 6 of which have changed. The differential backup contains only these 6 data extents. The differential backup operation relies on a bitmap page that contains a bit for every extent. For each extent updated since the base, the bit is set to 1 in the bitmap.
.gif "Differential bitmap identifies changed extents")
Note
The differential bitmap is not updated by a copy-only backup. Therefore a copy-only backup cannot serve as a differential base or differential backup. A copy-only backup does not affect subsequent differential backups.
A differential backup that is taken fairly soon after its base is usually significantly smaller than the differential base. This saves storage space and backup time. However, as a database changes over time, the difference between the database and a specific differential base increases. The longer the time between a differential backup and its base, the larger the differential backup is likely to be. This means that the differential backups can eventually approach the differential base in size. A large differential backup loses the advantages of a faster and smaller backup.
At restore time, before you restore a differential backup, you must restore its base. Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. Typically, you would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.
When you create and restore differential backups, SQL Server 2005 treats the database as a set of files. This affects the contents of differential backups and how they are used in combination with database and file backups. The SQL Server Database Engine is designed to handle the common scenarios easily and without any unexpected behavior.
For more information, see Base of a Differential Backup.
See Also
Concepts
Differential Database Backups
Differential Partial Backups
Differential File Backups