Home Library Learn Downloads Support Community
Sign in | United States - English | |
This topic has not yet been rated - Rate this topic

Recovering to a Specific Time

SQL Server 2005

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

ms178143.note(en-US,SQL.90).gifNote:
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.

Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ