Best Practices for Recovering a Database to a Specific Recovery Point

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.

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.

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

Help and Information

Getting SQL Server 2005 Assistance