How to: Restore to a Point in Time (Transact-SQL)
This topic explains how to restore to a point in time.
To restore to a point in time
Execute the RESTORE DATABASE statement using the NORECOVERY option.
Execute the RESTORE LOG statement to apply each log backup, specifying:
- The name of the database to which the transaction log is applied.
- The backup device from where the transaction log backup is restored.
- The RECOVERY and STOPAT options. If the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered.
Example
The following example restores the AdventureWorks database to its state as of 12:00 AM on April 15, 2005. The restore sequence installs a full database backup by using the NORECOVERY option and applies three log backups, specifying the RECOVERY and STOPAT options in each RESTORE LOG statement. The backup device is a logical backup device named AdventureWorksBackups.
Important
The AdventureWorks database uses the simple recovery model. To permit log backups, before taking a full database backup, the database was set to use the full recovery model, using ALTER DATABASE AdventureWorks SET RECOVERY FULL.
-- Restore the full database backup.
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH NORECOVERY;
GO
RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM';
GO
RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM';
GO
RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM';
GO
See Also
Concepts
Other Resources
RESTORE (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL)