Restoring a Database to a Point Within a Backup

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

  • 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.

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

Help and Information

Getting SQL Server 2005 Assistance