Recovering to a Point In Time

Administering SQL Server

Administering SQL Server

Recovering to a Point In Time

You can recover to a point in time by recovering only the transactions that occurred before a specific point in time within a transaction log backup, rather than the entire backup. By viewing the header information of each transaction log backup or the information in the backupset table in msdb, you can quickly identify which backup contains the time to which you want to restore the database. You then need only apply transaction log backups up to that point.

You cannot skip specific transactions. 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 restore any modifications made to the database after a specific point in time:

  • Restore the last database backup without recovering the database.

  • Apply each transaction log backup in the same sequence in which they were created.

  • Recover the database at the desired point in time within a transaction log backup.

This process also can be used to restore a database and transaction logs if some transaction log backups created after a point in time are missing or damaged.

To restore to a point in time