Differential backups

There are two distinct types of differential backups: single-base (which is a differential backup that’s based on a single full backup) and multibase (which is a differential backup that’s based on different file or file group backups). The most commonly used type is the single-base differential backup. You must run a single-base differential backup on databases operating under the simple recovery model. If you try to run a multibase differential backup, you’ll receive an error and the differential backup will fail.

Differential backups are totally independent from the transaction log backups and won’t interrupt the log sequence number (LSN) chain or log shipping. Implementing differential backups can greatly reduce the number of required restores, as the volume of transaction log backups could be large over a week. Restoring a differential backup will bring the database up-to-date more quickly.

Copy-only backups don’t affect differential backups, so it’s possible to use the WITH COPY_ONLY option to perform full backups without interrupting normal differential backup management. A copy-only full backup can’t be used as a differential base for restores.

The benefits of using differential backups can be huge. Most databases rarely change more than 3 percent to 5 percent per day, so the differential backup file (with or without backup compression) will be significantly smaller in size than a full backup file and will slowly increase in size each day as the data changes.

Information about differential backups is stored in the sys.database_files catalog view and in sys.master_files catalog view. If the database is read-only then only the sys.master_files catalog view is updated.

How to Restore Differential Backups

To demonstrate (refer figure 2) how to restore differential backups, let’s suppose that a system failure occurred at 2:30 p.m. on Tuesday.

1.       The first thing you’d want to do is perform a tail log backup, which is a transaction log backup that includes the portion of the log that hasn’t previously been backed.

2.       Now you need to restore the latest differential backup (the one taken at 6 p.m. on Monday) using the RESTORE DATABASE command with the NORECOVERY option.

3.       After that’s done, you can restore the transaction log backups that were taken after the differential backup. So, for this example, you need to restore the log backups taken at 9 p.m. on Monday and at midnight, 3 a.m., 6 a.m., 9 a.m., and noon on Tuesday. 

4.       At this point, you need to restore the tail log backup using the RESTORE LOG command with the NORECOVERY option to bring the database up to the point in time that the failure occurred (2:30 p.m. on Tuesday).

5.       Finally, you can bring the database online using the RESTORE statement with the RECOVERY option.