Share via

Restore Database (Options Page)

  • Article

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

Note

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).

Options

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).

    Warning

    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

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).

    Note

    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.

See Also

Other Resources

RESTORE (Transact-SQL)
How to: Restore a Backup from a Device (SQL Server Management Studio)
How to: Restore a Transaction Log Backup (SQL Server Management Studio)
Media Sets, Media Families, and Backup Sets
Working with Transaction Log Backups

Help and Information

Getting SQL Server 2005 Assistance