Restore Database (Options Page)

Use the Options page of the Restore Database dialog box to modify the behavior and outcome of the restore operation.

To use SQL Server Management Studio to restore a database backup

When you specify a restore task by using SQL Server Management Studio, you can generate a corresponding Transact-SQL script containing the RESTORE statements for this restore operation. To generate the script, click the Script button and then selecting a destination for the script. For information about the RESTORE syntax, see RESTORE (Transact-SQL).

Restore options

Optionally, to modify aspects of the behavior of the restore operation, use the options of the Restore options panel.

Overwrite the existing database

Specifies that the restore operation will overwrite the files of any database that is currently using the database name that you are specifying in the To database field on the General page of the Restore Database dialog box. The files of the existing database will be overwritten even if you are restoring backups from a different database to the existing database name. Selecting this option is equivalent to using the REPLACE option in a RESTORE statement (Transact-SQL).

Use this option only after careful consideration. For more information, see Using the REPLACE Option.

Preserve the replication settings

Preserves the replication settings when restoring a published database to a server other than the server where the database was created. This option is relevant only if the database was replicated when the backup was created.

This option is available only with the Leave the database ready for use by rolling back the uncommitted transactions option (described later in this table), which is equivalent to restoring a backup with the RECOVERY option.

Selecting this option is equivalent to using the KEEP_REPLICATION option in a RESTORE statement.

For more information, see Backing Up and Restoring Replicated Databases.

Prompt before restoring each backup

Specifies that after each backup is restored, the Continue with Restore dialog box will be displayed to inquire whether you want to continue the restore sequence. This dialog box displays the name of the next media set (if known) and the name and description of the next backup set.

This option allows you to pause a restore sequence after restoring any of the backups. This option is particularly useful when you must swap tapes for different media sets; for example, when your server has only one tape device. When you are ready to proceed, click OK.

You can interrupt a restore sequence by clicking No. This leaves the database is in the restoring state. At your convenience, you can later continue the restore sequence by resuming with the next backup described in the Continue with Restore dialog box. The procedure restoring the next backup depends on whether it contains data or transaction log, as follows:

Restrict access to the restored database

Makes the restored database available only to the members of db_owner, dbcreator, or sysadmin.

Selecting this option is synonymous to using the RESTRICTED_USER option in a RESTORE statement.

Restore the database files as

Displays a grid that lists the original full path for each data or log file of the database and the restore destination for each file. You can move the database you are restoring by specifying new restore destinations for the files.

The following table describes the columns of the Restore the database files as grid.

Header Values

Original File Name

The full path of a data file or log file of the original database.

Restore As

The path and filename to be used as the restore destination for this data file or log file.

To restore a database to a new location, you must edit the Restore As field for each file and specify a new destination path and/or filename. Changing the restore destinations in the Restore As column is equivalent to using the MOVE option in RESTORE statements.

If you want to restore a copy of a database on the server instance while the original database is still attached, you must also specify a new database name in the To database field on the General page.

Recovery state

To determine the state of the database after the store operation, you must select one of the options of the Recovery state panel.

Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)

Recovers the database after restoring the final backup checked in the Select the backup sets to restore grid on the General page. This is the default option and is equivalent to specifying WITH RECOVERY in a RESTORE statement (Transact-SQL).

Under the full recovery model or bulk-logged recovery model, choose this option only if you are restoring all the log files now.

Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)

Leaves the database in the restoring state. This allows you to restore additional backups in the current recovery path. To recover the database, you will have to perform a restore operation by using the RESTORE WITH RECOVERY option (see the preceding option).

This option is equivalent to specifying WITH NORECOVERY in a RESTORE statement.

If you select this option, the Preserve replication settings option is unavailable.

Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

Leaves the database in a standby state, in which the database is available for limited read-only access. This option is equivalent to specifying WITH STANDBY in a RESTORE statement.

Choosing this option requires that you specify a standby file in the Standby file text box. The standby file allows the recovery effects to be undone.

Standby file

Specifies a standby file. You can browse for the standby file or enter its pathname directly in the text box.