Restoring a Database to a Prior State

By using a database backup and a sequence of transaction log backups, it is possible to restore a database to any point in time encompassed within those transaction logs.

Restoring to the Point of Failure

If, for example, a failure requires that the database needs to be restored, you can restore a database to the state it was in at the point of failure if the current transaction log file for the database is available, and all the conditions for backing up a transaction log are satisfied. To restore the database to the point of failure:

• Back up the currently active transaction log. For more information, see Creating and Applying Transaction Log Backups.
• Restore the database backup without recovering the database.
• Apply each transaction log backup in the same sequence in which they were created without recovering the database.
• Apply the backup of the current transaction log file as the last backup in the chain, and recover the database.

Restoring to a Prior State

You may want to restore the database to an earlier point in time than to the point of failure. For example, an earlier transaction within a database changed some data incorrectly, but the transaction was committed, and several transaction log backups have been created afterward. Therefore, you will need to restore the database backup and only those transaction logs that contain the changes prior to the transaction you do not want applied.

Note It is not possible to omit a transaction selectively from being applied (applying transactions that occur after the omitted transaction) because this would compromise the integrity of the data in the database. Any transactions that occur after the transaction you want to undo might depend on the data modified by the undone transaction.

If you do not want to attempt to restore any modifications made to the database after a specific transaction log backup was created:

• Restore the last database backup without recovering the database.
• Apply each transaction log backup in the same sequence in which they were created.

  Do not recover the database until the last transaction log you want to recover is applied.

This process can also be used to restore a database and any transaction logs if some transaction log backups created after a point in time are missing or damaged or the conditions for backing up a transaction log are not satisfied (for example, nonlogged operations have occurred).

Restoring to a Point within a Transaction Log Backup

You can also instruct recovery to apply only transactions that occurred before a specific point in time within a transaction log backup, rather than applying the entire backup. By viewing the header information of each transaction log backup, you can examine each transaction log backup to identify quickly which backup contains the time to which you want to restore and then apply only those transaction log backups up to and including the identified backup.

To create a transaction log backup

To create a backup of the currently active transaction log

To restore to the point of failure

To restore to a point in time

See Also