• Article

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)).

Use STANDBY to Find Unknown Point in Time

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).

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.

Important

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:

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.

Change History

Updated content

Corrected "Use STANDBY to Find Unknown Point in Time" section to clarify that WITH STANDBY merely enables you to undo the effects the recovery process.