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

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

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;

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.

Concepts

Other Resources

Help and Information