• Article

Restoring a Database to a Point Within a Backup

 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 following illustration shows a restore to a recovery point in the middle of a transaction log that was taken at time t9. Changes in the remainder of this backup and the subsequent log backup that was taken at time t10 are discarded.

Restoring to point in the middle of a log backup

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.

A specified time or transaction is always restored from a log backup, so the target recovery point must be contained in a transaction log backup. To restore a database to a specific point in time or transaction, specify the target recovery point in a STOPAT, STOPATMARK, or STOPBEFOREMARK clause. In every RESTORE LOG statement of the restore sequence, you must specify your target time or transaction in an identical STOPAT, STOPATMARK, or STOPBEFOREMARK clause. When you apply the log backup that contains the recovery point, you can recover only transactions that come before that point.

As a prerequisite to a point-in-time restore, you must first restore a full database backup whose end point is earlier than your target recovery point. To help you identify which database backup to restore, you can optionally specify your WITH STOPAT, STOPATMARK, or STOPBEFOREMARK clause in a RESTORE DATABASE statement to raise an error if a data backup is too recent for the specified target time. But the complete data backup is always restored, even if it contains the target time.

Note

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.

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.

If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported. You can force the restore sequence to continue. However the FILESTREAM filegroups that are omitted from your RESTORE statement can never be restored. To force a point-in-time restore, specify the CONTINUE_AFTER_ERROR option together with the STOPAT, STOPATMARK, or STOPBEFOREMARK option, which you must also specify in your subsequent RESTORE LOG statements. If you specify CONTINUE_AFTER_ERROR, the partial restore sequence succeeds and the FILESTREAM filegroup becomes unrecoverable.

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