Megosztás a következőn keresztül:

  • Cikk

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

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

    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.

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

    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.

Considerations For Restoring a Database from an Earlier Version of SQL Server

  • After you restore a SQL Server 2005 or SQL Server 2000 database to SQL Server 2008, the database becomes available immediately and is then automatically upgraded. If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the Full-Text Upgrade Option server property. If the upgrade option is set to Import or Rebuild, the full-text indexes will be unavailable during the upgrade. Depending the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. Note also that when the upgrade option is set to Import, the associated full-text indexes are rebuilt if a full-text catalog is not available. For information about viewing or changing the setting of the Full-Text Upgrade Option property, see How to: View or Change Server Properties for Full-Text Search (SQL Server Management Studio).

  • The compatibility levels of the tempdb, model, msdb and Resource databases are set to 100 after upgrade. The master system database retains the compatibility level it had before upgrade, unless that level was less than 80. If the compatibility level of master was less than 80 before upgrade, it is set to 80 after upgrade.

    If the compatibility level of a user database was 80 or 90 before upgrade, it remains the same after upgrade. If the compatibility level was 70 or less before upgrade, in the upgraded database, the compatibility level is set to 80, which is the lowest supported compatibility level in SQL Server 2008.

    Note

    New user databases will inherit the compatibility level of the model database.