Introducing Arcserve Backup Agents and Options › Using Agents › Agent for Microsoft SQL Server › Restoring Microsoft SQL Server Databases › Log Point-in-Time Restore Options

Log Point-in-Time Restore Options

The Log Point-in-Time Restore option restores a database to the state it was in at a date and time, or as of a named transaction, that you have specified. You should use Automatic Selection with this option. This option is only available when the selected session is a Transaction Log backup.

Important! You cannot use the Log Point in Time Restore option if the database you are recovering uses the Bulk-Logged recovery model.

To find the correct log when you have selected the Log Point in Time Restore option, Microsoft SQL Server restores the record in each Transaction log backup containing the start and finish time of the backup. Microsoft SQL Server then searches this record for the time you have specified.

• If Microsoft SQL Server finds the specified time, it restores the log to the point in the record that contains the time you submitted. The agent then signals Arcserve Backup to stop restoring and the database is fully recovered. If there are other logs with the same time, those logs are ignored and the subsequent sessions are skipped.
• If the specified time occurs after those contained in the log, Microsoft SQL Server restores the log and leaves the database in a restoring state, waiting for the next log restore operation.
• If the specified time occurs before those contained in the log, Microsoft SQL Server cannot restore the log.

The Log Point in Time Restore option has limitations. For example, if you do not select Automatic Selection and choose the Force Restore Over Existing Files option and you restore one or more logs belonging to the same database, but you do not select the appropriate database, differential backup, and FileGroup sessions to be restored first, the job will be incomplete and the subsequent sessions for that database will be ignored.

The options available for Log Point in Time Restore are:

Stop at time

Includes date and time fields in which you can enter a specific date and time. The option recovers the database to the specified date and time. This is the default option.

Stop at log mark

The option recovers the database activity through the transaction which is marked with the specified name, including the transaction that contains the mark. If you do not select the After Date and Time option, recovery stops at the first marked transaction with the specified name. If you select the After Date and Time option, recovery stops at the first marked transaction with the specified name exactly at or after the specified date and time.

Note: This option is available in Microsoft SQL Server 2000 or later.

Stop before log mark

The option recovers the database activity prior to the transaction which is marked with the specified name. The transaction which contains the mark is not re-executed. If you do not select the After Date and Time option, recovery stops at the first marked transaction with the specified name. If you select the After Date and Time option, recovery stops at the first marked transaction with the specified name exactly at or after the specified date and time.

Note: This option is available in Microsoft SQL Server 2000 or later.

After Date and Time

Allows you to specify a point in time after which Microsoft SQL Server will look for the specified Log Mark. The recovery stops at the specified mark only when the timestamp for the log mark is later than the specified time. The same Date and Time entry fields are used as for the Stop at Time option. Use this option with the Stop at Log Mark or Stop Before Log Mark options.