Setting restore options for SQL

This procedure details how to select restore job properties for SQL, and provides definitions for SQL-specific restore options. For details on how to create a restore job, and for definitions of all other restore options:

See Restoring data by setting job properties.

To set restore options for SQL

  1. On the navigation bar, click the arrow next to Restore.

  2. Click New Restore Job.

  3. On the Properties pane, under Settings, click Microsoft SQL.

  4. Select the appropriate options as follows:

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

    Select this option to have 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.

    Select this option if you have additional differential or transaction log backups to be restored in another restore job.

    Leave the database in read-only mode

    Select this option during transaction log and database restore to create and maintain a standby database. See your SQL documentation for information on standby databases.

    Take existing destination database offline

    Select this option if you want Backup Exec to 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.

    This option is not supported under SQL 7.0.

    Overwrite the existing database

    Select this check box to replace 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

    Select this check box to let 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)

    Select this option to let Backup Exec restore as much of the SQL 2005 database as possible if SQL 2005 detects database corruption errors during the database restore.

    Run verify only; do not restore data

    Select this option to have 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) and with SQL 2005.

    See Setting backup options for SQL.

    Consistency check after restore

    The following options are available:

    • Full check, excluding indexes. Select this option to exclude 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. Select this option to include indexes in the consistency check. Any errors are logged. This option is selected by default.

    • Physical check only. Select this option to perform 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. Physical check only is not supported under SQL 7.0.

    • None. Select this option if you are doing 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

    Select this to recover all of the transactions in the transaction logs you select for restore.

    Point in time log restore

    Select this check box to restore 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, select 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

    Select this check box to restore 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.

    This option is not supported under SQL 7.0.

    Include the named transaction

    Select this check box to include 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.

    This option is not supported under SQL 7.0.

    Found after

    Select this check box to specify 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.

    This option is not supported under SQL 7.0.

    Check selections

    Click this button to have 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

    Click this to start a wizard that helps you select restore job properties for SQL.

More Information

About restoring SQL databases and file groups

Restoring from SQL transaction logs up to a point in time

Restoring from SQL 2000, SQL 2005 or SQL 2008 transaction logs up to a named transaction

Restoring from SQL filegroup backups