Recovering to a Specific 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
How to: Restore to a Point in Time (SQL Server Management Studio)
How to: Restore to a Point in Time (Transact-SQL)
How to: Restore to the Point of Failure (Transact-SQL)
|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.
ConceptsApplying Transaction Log Backups
Using Marked Transactions (Full Recovery Model)
Understanding How Restore and Recovery of Backups Work in SQL Server
Other Resourcesbackupset (Transact-SQL)
RESTORE HEADERONLY (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