Bookmark and Share
restore a DB STOPAT Option
(Publish Date: 2014-5-29 11:42am, Total Visits: 465, Today: 1, This Week: 2, This Month: 10)

Restore a db by following the following restore sequence:

RESTORE DATABASE database_name FROM full_backup
WITH NORECOVERY;

RESTORE DATABASE database_name FROM differential_backup
WITH NORECOVERY;

RESTORE LOG database_name FROM first_log_backup
WITH STOPAT = time, RECOVERY;

… (additional log backups could be restored here)

RESTORE LOG database_name FROM final_log_backup
WITH STOPAT = time, RECOVERY;

++ the RECOVERY option is specified on each of the RESTORE LOG commands, not just on the last command.

The behavior of the STOPAT and RECOVERY options is as follows:

• If the specified time is earlier than the first time in the transaction log backup, the restore command fails and returns an error.

• If the specified time is contained within the period covered by the transaction log backup, the restore command recovers the database at that time.

• If the specified time is later than the last time contained in the transaction log backup, the restore command restores the logs, sends a warning message, and the database is not recovered so that additional transaction log backups can be applied.