Udostępnij za pośrednictwem

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

  • Artykuł

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

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.

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

Recovering to a Specific Time

Other Resources

RESTORE (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance