Export (0) Print
Expand All

How to: Restore to a Point in Time (Transact-SQL)

This topic explains how to restore to a point in time.

  1. Execute the RESTORE DATABASE statement using the NORECOVERY option.

  2. 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.

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.

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

Community Additions

ADD
Show:
© 2014 Microsoft