Recovering to a Specific Time

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


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.

See Also


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)
Implementing Restore Scenarios for SQL Server Databases
Restoring a Database to a Point Within a Backup
Working with Restore Sequences for SQL Server Databases

Help and Information

Getting SQL Server 2005 Assistance