Reducing Recovery Time
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 a backup 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 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 one or more times per day, and transaction log backups every ten minutes.
If a database needs to be recovered to the point of failure, for example, due to a system failure:
- Back up the currently active transaction log. This operation will fail if the transaction log has been damaged.
- Restore the last database backup created.
- Restore the last differential backup created since the database backup was created.
- 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 cannot be backed up, it is possible to restore the database only to the point when the last transaction log backup was created. Changes made to the database since the last transaction log backup are lost and must be redone manually.
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:
- Restore the database backup created on Tuesday night.
- Restore the differential database backup created at 5:00 A.M. on Wednesday.
- Apply the transaction log backup created at 5:10 A.M. on Wednesday.
- 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:
- Restore the database backup created on Wednesday night.
- Restore the differential database backup created at 2:00 A.M. on Thursday.
- Apply all the transaction log backups created from 2:10 A.M. to 3:00 A.M. on Thursday.
- 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