Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
.gif "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
- 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)
- ToPointInTime (SMO)
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.
See Also
Concepts
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)
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