How to restore to a point in time (Transact-SQL)

To restore to a point in time

1. Execute the RESTORE DATABASE statement using the NORECOVERY clause.
2. Execute the RESTORE LOG statement to apply each transaction log backup, specifying:
   • The name of the database to which the transaction log will be applied.
   • The backup device from where the transaction log backup will be restored.
   • The RECOVERY and STOPAT clauses. 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.

   ---

   Note The STOPAT clause cannot be used with the STANDBY or NORECOVERY clauses.

   ---

Examples

This example restores a database to its state as of 10:00 A.M. on July 1, 1998, and illustrates a restore operation involving multiple logs and multiple backup devices.

-- Restore the database backup.

RESTORE DATABASE MyNwind

    FROM MyNwind_1, MyNwind_2

    WITH NORECOVERY

GO

RESTORE LOG MyNwind

    FROM MyNwind_log1

    WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

GO

RESTORE LOG MyNwind

    FROM MyNwind_log2

    WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

GO

See Also