Best Practices for Recovering a Database to a Specific Recovery Point
This topic is relevant only for SQL Server databases that use the full or bulk-logged recovery models. It presents some best practices for restoring a database to a specific recovery point (a point in time, a marked transaction, or a log sequence number (LSN)).
Sometimes you want to restore a database to a specific point in time, but you do not know exactly what point-in-time best meets your recovery goal (that is, your optimal recovery point). To find this recovery point, you can use RESTORE … WITH STANDBY = standby_file_name along with a STOPAT clause whose target time precedes the recovery point for which you are looking. A STANDBY restore operation recovers the database for read-only access, enabling you to inspect the database to look for your optimal recovery point.
By using a series of STANDBY restore operations you can incrementally roll forward the primary data file (and, optionally, other data files) until you find your optimal recovery point. After you have identified your optimal recovery point, you can roll the database forward again, specifying WITH STOPAT to specify your optimal recovery point as the target point in time. On reaching this target point in time, restore the partial database using the WITH 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).
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.
If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported. You can force the restore sequence to continue. However the FILESTREAM filegroups that are omitted from your RESTORE statement can never be restored. To force a point-in-time restore, specify the CONTINUE_AFTER_ERROR option together with the STOPAT, STOPATMARK, or STOPBEFOREMARK option, which you must also specify in your subsequent RESTORE LOG statements. If you specify CONTINUE_AFTER_ERROR, the partial restore sequence succeeds and the FILESTREAM filegroup becomes unrecoverable.
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:
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.
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.
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.