Using Differential Database Backups with Transaction Log Backups

Using database, differential database, and transaction log backups together can reduce the amount of time it takes to restore a database back to any point in time after the database backup was created. Additionally, creating both differential database and transaction log backups can increase the robustness of backup procedures in the event that either a transaction log backup or differential database backup becomes unavailable, for example, due to media failure.

Typical backup procedures using database, differential database, and transaction log backups are to create database backups at longer intervals, differential database backups at medium intervals, and transaction log backups at shorter intervals. For example, create database backups weekly, differential database backups daily, and transaction log backups hourly.

If a database needs to be recovered to the point of failure, for example, due to a system failure:

1. Back up the currently active transaction log if the transaction log files for the database are accessible.
2. Restore the last database backup created.
3. Restore the last differential backup created since the database backup was created.
4. Apply all transaction log backups, in sequence, created after the last differential backup was created, finishing with the transaction log backup created in Step 1.

Note If the active transaction log is not accessible, or the conditions for backing up the active transaction log are not satisfied, it is possible to restore the database only to the point when the last transaction log backup was created.

By using differential database and transaction log backups together to restore a database to the point of failure, the time taken to restore a database is reduced because only the transaction log backups created since the last differential database backup was created need to be applied. If a differential database backup was not created, then all the transaction log backups created since the database was backed up need to be applied.

For example, a mission-critical database system requires that a database backup is created each night at midnight, a differential database backup is created on the hour, Monday through Saturday, and transaction log backups are created every 10 minutes throughout the day. If the database needs to be restored to its state at 5:19 A.M. on Wednesday:

1. Restore the database backup created on Tuesday night.
2. Restore the differential database backup created at 5:00 A.M. on Wednesday.
3. Apply the transaction log backup created at 5:10 A.M. on Wednesday.
4. Apply the transaction log backup created at 5:20 A.M. on Wednesday, specifying that the recovery process only applies transactions that occurred before 5:19 A.M.

Alternatively, if the database needs to be restored to its state at 3:04 A.M. on Thursday, but the differential database backup created at 3:00 A.M. on Thursday is unavailable:

1. Restore the database backup created on Wednesday night.
2. Restore the differential database backup created at 2:00 A.M. on Thursday.
3. Apply all the transaction log backups created from 2:10 A.M. to 3:00 A.M. on Thursday.
4. Apply the transaction log backup created at 3:10 A.M. on Thursday, specifying that the recovery process only applies transactions that occurred before 3:04 A.M.

To create a database backup

To create a differential database backup

To create a transaction log backup

To create a backup of the currently active transaction log

To restore a database backup

To restore a differential database backup

To apply a transaction log backup

See Also