Reducing Recovery Time When Restoring a Database

  • Makale

Icon showing a blue database disk This topic is relevant only for databases that use the full or bulk-logged recovery models.

Using database, differential database, and transaction log backups together can reduce the amount of time it takes to restore a database 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 that use 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, you can create database backups weekly, differential database backups one or more times per day, and transaction log backups every ten minutes.

Recovering a Database to the Point of Failure

If a database needs to be recovered to the point of failure, do the following:

  1. Back up the currently active transaction log (the tail of the log). Note that, if the transaction log has been damaged, this operation will fail.
  2. Restore the most recent full database backup.
  3. Restore the most recent differential database backup created since the restored full database backup.
  4. Apply all transaction log backups, in sequence, that were created after the last differential backup, finishing with the tail-log backup you created in Step 1.

Note

If the tail of the log cannot be backed up, you can restore the database only to the time of the most recent regular log backup. Changes made to the database since that log backup are lost.

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 you need to apply only the transaction log backups that were created since the last differential database backup was created. If a differential database backup was not created, you need to apply all the transaction log backups that were created since the entire database was backed up.

Examples

Using Differential Database and Transaction Log Backups Together

This example assumes a mission-critical database system for which a full database backup is created daily 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. To restore the database to the state is was in at 5:19 A.M. Wednesday, do the following:

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

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

  1. Restore the database backup that was created Wednesday at midnight.
  2. Restore the differential database backup that was 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 that was created at 3:10 A.M. on Thursday, stopping the recovery process at 3:04 A.M.

Creating Backups

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 create a backup by using SQL Server Management Objects (SMO)

  • SqlBackup

Restoring Backups

To restore a database backup

To restore a differential database backup

To apply a transaction log backup

To restore a backup by using SQL Server Management Objects (SMO)

See Also

Concepts

Optimizing Backup and Restore Performance in SQL Server
Understanding Recovery Performance in SQL Server
Working with Transaction Log Backups

Other Resources

BACKUP (Transact-SQL)
RESTORE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance