Best Practices for Recovering a Database to a Specific Recovery Point
.gif "Icon showing a blue database disk")
This topic is relevant only for databases that use the full or bulk-logged recovery models.
This topic presents some best practices for restoring a database to a specific recovery point.
Use STANDBY to Find Unknown Point in Time
Sometimes, you want to restore a database to a specific recovery point, but you do not know the target time, LSN, or marked transaction, if any. One solution is to incrementally roll forward the primary data file and, optionally, other data files by specifying WITH STANDBY **=**standby_file_name instead of WITH RECOVERY. Using the STANDBY option recovers the database for read-only access. This lets you read the database and look for the point-in-time information that you want. The standby file lets you undo the effects the recovery process, in case the database is rolled forward beyond the target point-in-time. If the database has not yet reached your target point in time, you can restore successive log backups using WITH STANDBY, specifying the same standby file in each restore statement.
After you have identified the target recovery point, you can roll forward again to that point in time. On reaching the target point in time, restore the partial database using the RECOVERY option. The remaining files, if any, can then be restored and recovered to be consistent with the database. After you restore the last full log restore, restore the first log backup taken after the target recovery point.
For more information about the STANDBY option, see RESTORE Arguments (Transact-SQL).
Specify the Point in Time Early in a Restore Sequence
A restore sequence consists of one or more restore operations that move data through one or more of the phases of restore. The more information that you supply about the intent of the sequence, the less likely you are to experience errors that could require that you start over. For point-in-time restore, the SQL Server Database Engine uses information specified at the start of (and during) a restore sequence to identify the point-in-time goal of the restore. If you wait too long in the sequence, the sequence fails. You must provide the stop-at information early enough in the restore sequence to make sure that restore does not go beyond your point-in-time goal.
If you intend to stop at a particular recovery point, you should communicate this intention on every RESTORE LOG statement (by using STOPAT, STOPBEFOREMARK, or STOPATMARK), together with the RECOVERY option. When the RECOVERY and STOPAT options are used together, 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. When the recovery point is reached, the database is recovered, and an attempt to restore another log backup fails. Using the RECOVERY and STOPAT options together when restoring a log backup ensures that no log restore ever goes beyond the stop-at time.
Example: Point-in-Time Restore
In the following example, the restore sequence starts to declare the intention to stop at a particular time when applying the first log backup. In this example, the stop-at time occurs in the first log backup after the differential backup.
RESTORE DATABASE database_name FROM full_backup
WITH NORECOVERY;
RESTORE DATABASE database_name FROM full_differential_backup
WITH NORECOVERY;
RESTORE LOG database_name FROM log_backup
WITH STOPAT = time, RECOVERY;
RESTORE LOG database_name FROM log_backup
WITH STOPAT = time, RECOVERY;
Whether a specified log restore succeeds depends on whether time is in the interval captured by a log backup, as described in the following table.
| Relationship of time to the interval captured by the log backup: | Effect |
|---|---|
time is before the interval. |
The restore fails, and no roll forward occurs. |
time is during the interval. |
The last restore succeeds, and the database is recovered. |
time is after the interval. |
Roll forward succeeds, but the database is not recovered, because time has not yet been reached. |
Declare the Stopping Point on Every Subsequent RESTORE Statement
A stopping point is specific to the statement that specifies the STOPAT, STOPBEFOREMARK, or STOPATMARK option. If you omit that option from a RESTORE statement, the complete backup is restored.
As a point-in-time restore sequence progresses, you can change the recovery point by specifying a new value for time, assuming that the database has not yet gone beyond the new time.
Note
The STOPBEFOREMARK and STOPATMARK options have two parameters, mark_name and lsn_number. The mark_name parameter, which identifies a transaction mark in a log backup, is supported only in RESTORE LOG statements. The lsn_number parameter, which specifies a log sequence number, is supported in both RESTORE DATABASE statements and RESTORE LOG statements.
See Also
Concepts
Applying Transaction Log Backups
Recovering to a Log Sequence Number (LSN)
Recovering to a Marked Transaction
Other Resources
RESTORE (Transact-SQL)
Restoring a Database to a Point Within a Backup