Using Differential Backups

The main advantage of a full database backup is that it contains all the data needed to rebuild the entire database. But this advantage can also be a disadvantage. Consider a database where full database backups are performed each night. If you need to recover the database, you always have to use the backup from the previous night, resulting in the loss of a whole day's work. One way to reduce the potential period of time that can be lost would be to perform full database backups more often. But this itself can be a problem. Because all data and parts of the transaction log are written to the backup device, it can be very time-intensive to make a backup. Also, you need a lot of storage space to hold these backups, and a full backup can decrease the performance of your database as a result of the large amount of I/O it requires. Wouldn't it be better to perform one full database backup at night and only take backups of data changes made during the day? This sort of functionality is provided by the differential backup.

The differential backup stores only the data changes that have occured since the last full database backup. When the same data has changed many times since the last full database backup, a differential backup stores the most recent version of the changed data. Because it contains all changes since the last full backup, to restore a differential backup, you first need to restore the last full database backup and then apply only the last differential backup, as shown in Figure 3-1. Like the full database backup, the differential backup includes a part of the transaction log in order to recover to a consistent state.

Figure 3-1. Backup strategy with differential backups.

Performing Differential Backups

Performing a differential backup is very similar to performing full database backups. The only difference is that you state in the WITH option of the backup that you want to perform a differential backup. The syntax of the BACKUP DATABASE statement to perform a differential backup of AdventureWorks to a physical device, overwriting other existing backups on the backup device, is as follows:

USE master; GO BACKUP DATABASE AdventureWorks TO DISK='t:\adv_diff.bak' WITH INIT,DIFFERENTIAL;

If you want to use logical devices, you must create them first just as with the full database backup.

USE master; GO EXEC sp_addumpdevice 'disk', 'Adv_Diff_Dev', 'T:\BACKUPS\AdvDiffDev.bak'; GO BACKUP DATABASE AdventureWorks TO Adv_Diff_Dev WITH INIT,DIFFERENTIAL;

Important

To restore a differential backup, you always also need the most recent full database backup. Be careful not to overwrite or delete the full database backup as long as you require it for differential backups.