Recovering to a Specific Time

Article
12/03/2008

Icon showing a blue database disk 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

Note

The syntax for specifying a specific time or a marked transaction is unchanged from Microsoft SQL Server 2000, but now applies to RESTORE DATABASE and RESTORE LOG.

For restoring a database to a specific point in time, the RESTORE statement provides the STOPAT option, which is typically used when restoring a log backup.

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.

Recovering to a Specific Time

Transact-SQL Syntax for Restoring to a Specific Time

See Also

Concepts

Other Resources

Help and Information

Additional resources