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.
Point-in-time recovery is not supported with the Bulk-Logged Recovery model. Bulk-Logged recovery only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.