Introduction to SQL Server differential backups

This backup and restore topic is associated with all SQL Server databases.

A differential backup is based on the most recent full data backup. A differential backup captures only data that has changed since the full backup. The full backup on which the differential backup is based is called the "baseline" of the variance. Full backups (except replication backups only) can be used as a benchmark for a series of differential backups, including database backups, partial backups, and file backups. A base backup of a file differential backup can be included in a full backup, a file backup, or a partial backup.

Content of this topic:

Advantages

Differential Backup Overview

Differential backup of a read-only database

Related Tasks

Advantages

Creating a differential backup can be very fast compared to creating a full backup. A differential backup records only the data that has changed since the full backup on which the differential backup was based. This helps to make frequent data backups and reduce the risk of data loss. However, before you can restore a differential backup, you must first restore its baseline. Therefore, restoring from a differential backup is bound to take more steps and time than restoring from a full backup, as this requires two backup files.

Differential database backups are particularly useful if a subset of the database is modified more frequently than the rest of the database. In these cases, using a differential database backup, you can perform backups frequently and do not incur the overhead of a full database backup.

Under the full recovery model, using differential backups can reduce the number of log backups that must be restored.

Differential Backup Overview

A differential backup captures the state of any "zone" (a set of physically contiguous eight pages) that has changed between creating a differential baseline and creating a differential backup. This means that the size of a given differential backup depends on the amount of data that has changed since the differential base was established. Typically, the older the Difference datum is, the larger the new differential backup will be. In a series of differential backups, areas that are frequently updated may contain different data in each differential backup.

The following figure shows how differential backups work. The figure shows 24 data areas, six of which have changed. A differential backup contains only these six data areas. The differential backup operation depends on the bitmap page, which contains one bit for each zone. For each area that is updated since the differential datum was established, the bit is set to 1 in the bitmap.

Attention
A copy-only backup cannot update the differential bitmap. Therefore, replication-only backups do not affect subsequent differential backups.

A differential backup that is performed immediately after the baseline is established is usually significantly smaller than the differential baseline. This can save storage space and backup time. However, when the database changes over time, the difference between the database and the specific differential datum increases. The longer a differential backup is with its baseline interval, the greater the differential backup may be. This means that the size of the differential backup will eventually be close to the difference base size. Larger differential backups will lose the faster, smaller advantage of backup.

When the size of a differential backup increases, restoring a differential backup significantly prolongs the time it takes to restore the database. Therefore, it is recommended that you perform a new full backup at set intervals to create a new differential base for the data. For example, you can perform a full backup of the entire database (that is, a full database backup) Once a week, and then perform a series of regular differential database backups within that week.

During the restore process, the baseline must be restored before the differential backup is restored. You can then roll the database forward to the time the differential backup was created by simply restoring the most recent differential backup. Typically, you should restore the most recent full backup before restoring the most recent differential backup based on that full backup.

Differential backup of a read-only database

For read-only databases, using a full backup alone is easier to manage than using both full and differential backups. When the database is read-only, backups and other operations cannot change the metadata contained in the file. Therefore, the metadata required by a differential backup, such as the log sequence number at which the differential backup starts, or the differential baseline LSN, is stored in the Master database. If a differential datum is used when the database is read-only, the differential bitmap indicates more changes than actually occurred after the base backup. Additional data is read by the backup, but not written to the backup, because the differential_base_lsn stored in the Backupset system table is used to determine whether the data was actually changed after the baseline.

When you rebuild, restore a read-only database, or detach and reattach a read-only database, you lose the differential baseline information. This is because the Master database is out of sync with the user database. The SQL Server database engine is unable to detect or prevent the occurrence of this problem. All subsequent differential backups are not based on the most recent full backup, which can result in unexpected results. To establish a new differential baseline, it is recommended that you first create a full database backup.