Restoring a Database to a Point Within a Backup
.gif "Icon showing a blue database disk")
This topic is relevant only for SQL Server databases that use the full or bulk-logged recovery models.
You may want to restore and recover a database to a particular point in time, mark, or log sequence number (LSN) before the point of a failure. For example, if a transaction changed some data incorrectly, you might want to restore the database to a recovery point just before the incorrect data entry. Any restore that specifies the recovery point for the database is known as a point-in-time restore.
The recovery point for a point-in-time restore is usually within a transaction log backup. This is the last backup used in the point-in-time restore sequence. When you apply this log backup, you can recover only transactions that come before that point by specifying the target recovery point in a STOPAT, STOPATMARK, or STOPBEFOREMARK clause. When recovery finishes, the time of the database is determined by the recovery point to which you recovered the primary file. Subsequent restores, if any, must have recovery points that are consistent with the database at that time.
The target recovery point is specified by using one of the following:
- A specific point in time within a transaction log.
- A named mark that has been inserted into a transaction log record.
- A log sequence number (LSN).
Note
Recovering to an LSN is a specialized feature that is intended for tools vendors and is unlikely to be generally useful.
The target recovery point must be contained in a transaction log backup. Also, the log backup must be on the same recovery path as the full database or partial backup that is restored at the start of the point-in-time restore sequence.
Note
Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. The database must be recovered to the end of a transaction log backup.
In This Section
- Best Practices for Recovering a Database to a Specific Recovery Point
Presents several best practices for restoring a database to a specific recovery point.
- Recovering to a Specific Time
Contains information about how to recover to a point in time by recovering only the transactions that occurred before a specific point in time within a transaction log backup, instead of the whole backup.
- Recovering to a Marked Transaction
Contains information about how to recover to or just before a mark that was previously inserted into the transaction log.
- Recovering to a Log Sequence Number (LSN)
Contains information about how to use a log sequence number (LSN) to define the recovery point for a restore operation.
See Also
Concepts
Applying Transaction Log Backups
Using Marked Transactions (Full Recovery Model)
Understanding How Restore and Recovery of Backups Work in SQL Server
Other Resources
backupset (Transact-SQL)
RESTORE (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL)
Implementing Restore Scenarios for SQL Server Databases
Working with Restore Sequences for SQL Server Databases