Differential Database Backups

You can increase the speed of your backup operations (hence reducing the time to create backups) by supplementing the use of database backups with making occasional differential database backups. A differential database backup records only those data changes made to the database after the last full database backup. A differential database backup is smaller and takes less time to complete than a database backup. By creating differential database backups more frequently than database backups, you can decrease the amount of data you risk losing.

Unlike transaction log backups, differential database backups do not allow a database to be restored to the exact point of failure; only to the point in time that the differential database backup was created. Therefore, differential database backups are often supplemented by creating multiple transaction log backups after each differential database backup is created. By using a combination of database, differential database, and transaction log backups, recovery time and the amount of potential data loss due to failure can be minimized.

Some of the characteristics of a system for which you would consider using differential database backups include:

• The importance of the data is low enough that losing any modifications made after the last differential database backup can be tolerated because it is more efficient to endure the loss and possibly re-create the data manually rather than use transaction log backups.
• You need to implement simpler maintenance procedures than using transaction logs, for example, because you do not have a database administrator.
• The resources required to perform only database backups are limited, such as lack of backup storage space or time available to perform the backup operation.

  For example, a 10 terabyte database would require a lot of disk space and time to back up regularly in its entirety.

• Minimizing recovery time and loss of any transactions due to failure by using differential database backups in combination with database and transaction log backups.

A recommended process for backing up a database and any differential database backups is:

1. Back up the database regularly, such as every night.
2. Create a differential database backup periodically, such as every four hours or more for highly active systems.
3. Optionally, create transaction log backups between each differential database backups, such as every 30 minutes.

The process for restoring the database and differential database backups is:

1. Restore the most recent database backup.
2. Restore the last differential database backup.
3. Optionally, apply all transaction log backups created after the last differential database backup was created.

See Also