SQL restore options

You can set the following options when you create a restore job for SQL.

See SQL restore options.

See Restoring from SQL database backups.

See Restoring from SQL transaction logs up to a point in time .

Table: SQL restore options



Leave the database ready to use; additional transaction logs or differential backups cannot be restored

Lets the restore operation roll back all uncompleted transactions when you restore the last database, differential, or log backup. After the recovery operation, the database is ready for use. If you do not select this option, the database is left in an intermediate state and is not usable.

If you select this option, you cannot continue to restore backups. You must restart the restore operation from the beginning.

Leave the database nonoperational; additional transaction logs or differential backups can be restored

Indicates that you have additional differential or transaction log backups to be restored in another restore job.

Leave the database in read-only mode

Creates and maintains a standby database during a transaction log and database restore. See your SQL documentation for information on standby databases.

Take existing destination database offline

Lets Backup Exec automatically take the database offline before the restore job runs. If this option is not selected and there are active connections to the SQL database, the restore job will fail.

Overwrite the existing database

Replaces a database or filegroup, even if another database or filegroup with the same name already exists on the server. If Overwrite the existing database is not specified for a restore, SQL performs a safety check to ensure that a different database or filegroup is not accidentally overwritten. Refer to your SQL documentation for more information about the safety check that occurs when this option is not selected.

Automate master database restore

Lets Backup Exec stop SQL so that the master database can be restored. All existing users are logged off, and SQL Server is put into single-user mode.

When this option is selected, only the master database can be restored; if this option is selected for any other database, those jobs will fail.

If Backup Exec does not have access to the SQL registry keys, HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server, and HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer, then a restore to the default directory may not work, and the Automate master database restore option on the restore job properties for SQL will not work. To ensure that Backup Exec has access rights, verify that the logon account used has administrator rights to the Windows server that the SQL instance is installed on.

Continue restoring if an error occurs during the restore (SQL 2005 or later)

Lets Backup Exec restore as much of the SQL database as possible if SQL detects database corruption errors during the database restore.

Run verify only; do not restore data

Lets SQL verify your SQL backup jobs. This option returns the entire Backup Exec SQL data stream directly to SQL for verification. Although SQL processes the data stream for errors, existing SQL databases are not affected; all verification processes are handled within SQL itself, and nothing is ever written to the disk.

As SQL processes the data streams, a slight performance impact on overall database performance occurs until the verification process finishes.

Although supported in SQL 2000, this option’s best performance occurs with the Backup Exec SQL backup option, Use checksum on backups (SQL 2005 or later).

See Setting backup options for SQL.

Consistency check after restore

Specifies one of the following options:

  • Full check, excluding indexes.

    Excludes indexes from the consistency check. If indexes are not checked, the consistency check runs significantly faster but is not as thorough. Only the data pages and clustered index pages for each user table are included in the consistency check. The consistency of the nonclustered index pages is not checked.

  • Full check, including indexes.

    Includes indexes in the consistency check. Any errors are logged. This option is selected by default.

  • Physical check only.

    Performs a low overhead check of the physical consistency of the SQL 2000 database. This option only checks the integrity of the physical structure of the page and record headers, and the consistency between the pages’ object ID and index ID and the allocation structures.

  • None.

    This option is for sequential restores. Do not run a consistency check after a restore until all sequential restores have been done. If a consistency check is selected during a restore, the restore will complete but the consistency check will not be done. Check the job log for this information.

If you need to recover the database after restores are complete, select one of the consistency checks mentioned above.

Recover the entire log

Recovers all of the transactions in the transaction logs you select for restore.

Point in time log restore

Restores transactions from a transaction log up to and including a point in time in the transaction log. After the point in time, recovery from the transaction log is stopped.

In the Date box, select the part of the date you want to change, and then enter a new date or click the arrow to display a calendar from which you can select a date.

In the Time box, sele
ct the part of the time you want to change, and then enter a new time or click the arrows to select a new time.

Restore log up to named transaction

Restores transactions from a transaction log up to a named transaction (or named mark) in the transaction log; after that, recovery from the transaction log is stopped. The named transactions are case-sensitive.

Check your client application event log to find dates and times of named transactions.

Include the named transaction

Includes the named transaction in the restore; otherwise the restore will stop immediately before the named transaction is restored.

This option is only available if you select the Restore log up to named transaction option.

Found after

Specifies a date and time after which the restore operation is to search for the named transaction. For example, if you specify a restore from a log up to the named transaction AfternoonBreak, found after 6/02/2000, 12:01 p.m., then the restore operation will not search for AfternoonBreak until after that time.

This option is only available if you selected the Restore log up to named transaction option.

Check selections

Lets Backup Exec verify or complete the selections required to successfully restore SQL databases. After making your database restore selections, use this feature to verify the database selections are valid. If there are selection issues, Backup Exec notifies you of the error or errors and then corrects them for you.

Guide Me

Starts a wizard that helps you select restore job properties for SQL.

SQL restore options