• Article

Recovering to a Specific Point in Time

This topic is relevant only for SQL Server databases that use the full or bulk-logged recovery models.

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 the transaction log backup.

To restore to a specific time

Transact-SQL Syntax for Restoring to a Specific Time

A specified time is always restored from a log backup. In every RESTORE LOG statement of the restore sequence, you must specify your target time or transaction in an identical STOPAT clause. 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 restore time. That full database backup can be older than the most recent full database backup as long as you then restore every subsequent log backup, up to and including the log backup that contains your target point in time.

To help you identify which database backup to restore, you can optionally specify your WITH STOPAT clause in your RESTORE DATABASE statement to raise an error if a data backup is too recent for the specified target time. The complete data backup is always restored, even if it contains the target time.

The basic syntax is:

RESTORE LOG database_name FROM <backup_device> WITH STOPAT =time, RECOVERY…

The recovery point is the latest transaction commit that occurred at or before the datetime value that is specified by time.

To restore only the modifications that were made before a specific point in time, specify WITH STOPAT = time for each backup you restore. This makes sure that you do not go past the target time.

Generally, a point-in-time restore sequence involves the following stages:

  • Restore the last full database backup and, if any, the last differential database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).

  • Apply each transaction log backup in the same sequence in which they were created, specifying the time at which you intend to stop restoring log (RESTORE DATABASE database_name FROM <backup_device> WITH STOPAT**=time,** RECOVERY).

For more information, see Restoring a Database to a Point Within a Backup.