Are Differential Backups Necessary?

SQL Server natively supports 3 types of backups: Full, Differential and Log. Full backups take a complete backup of the entire database, while Log backups take a backup of the database’s transaction log. So, What are Differential backups? Are they really necessary?

Simply put, Differential Backups contain the data that has changed since the most recent Full Backup (excluding any copy-only backups). As transactions alter (modify or insert) more of the data, the Differential Backup will grow until it encompasses all of the data contained within a Full Backup.

In the example above, each backup encompasses the changes made to the database by the transactions below it. Differential Backups #1 & #2 are dependent upon Full Backup #1, in that they cannot be restored until Full #1 has. Differential #1 includes the changes made since Full Backup #1 was taken (transactions 7 through 11), whereas Differential #2 includes changes made by transactions 7 through 14. Also of note: Differential backups do not truncate the transaction log of databases that are in Full or Bulk-Logged recovery model. To keep the transaction logs of these databases from growing excessively, Transaction Log backups are required.

Back to the original question: Are differential backups really necessary?

As with many things in the database realm, the answer is: It Depends.

From a Recovery Point Objective standpoint, there are 2 processes by which a database can be restored to a specific point-in-time using native SQL backups. In the first process, differential backups are NOT required to recover the database. In the second process, they ARE required.

Process 1: Recover using Full and Log backups

1. Restore the most recent Full backup prior to the desired point-in-time.
   

   RESTORE DATABASE <database_name> FROM <backup_device> WITH NORECOVERY;

2. Restore each subsequent Log backup between the Full backup and the desired point-in-time, using NORECOVERY and STOPAT.
   

   RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY;    
   RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY, STOPAT = '<recovery_date_time>';

Process 2: Recover using Full, Differential and Log backups

1. Restore the most recent Full backup prior to the desired point-in-time.
   

   RESTORE DATABASE <database_name> FROM <backup_device> WITH NORECOVERY;

2. Restore the most recent Differential backup prior to the desired point-in-time.
   

   RESTORE DATABASE <database_name> FROM <backup_device> WITH NORECOVERY;

3. Restore each subsequent Log backup between the Differential backup and the desired point-in-time, using STOPAT.
   

   RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY;    
   RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY, STOPAT = '<recovery_date_time>';

Why use Differential backups if they aren’t required?

The basic answer to that question is: Time. By using Differential backups, the number of Transaction Log backup files that have to be processed is reduced. This will usually result in a database that is back online sooner (which everyone will be happier about).

The key things to remember are:

• Databases can be restored to a point-in-time without using differential backups.
• Differential backups do not truncate the Transaction Log.
• Differential backups can be used to speed up the database recovery process.