Restore Files and Filegroups (Options Page)

Use the Options page of the Restore Files and Filegroups dialog box to specify additional options for restoring a file or filegroup.

To restore a file backup by using SQL Server Management Studio

Note

When you specify a restore task by using SQL Server Management Studio, you can generate the corresponding Transact-SQL RESTORE script by clicking the Script button and then selecting a destination for the script.

Options

  • Restore as filegroup
    Indicates that an entire filegroup is being restored.

  • Overwrite the existing database
    Specifies that the restore operation should overwrite any existing databases and their related files, even if another database or file already exists with the same name.

    Selecting this option is equivalent to using the REPLACE option in a Transact-SQL RESTORE statement.

  • Prompt before restoring each backup
    Asks you for confirmation before restoring each backup set.

    This option is particularly useful where you must swap tapes for different media sets, such as when the server has one tape device.

  • 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 Transact-SQL RESTORE statement.

  • Restore the database files as
    Displays the original database file names. You can change the path and name of any of the files to which you are restoring.

    The following table lists the column headings of the grid and describes their values.

    Header

    Values

    Original File Name

    The full path of a source backup file.

    File Type

    Specifies the type of data in the backup: Data, Log, or Filestream Data. Data that is contained in tables is in Data files. Transaction log data is in Log files. Binary large object (BLOB) data that is stored on the file system is in Filestream Data files.

    Restore As

    The full path of the database file to be restored. To specify a new restore file, click the text box and edit the suggested path and file name. Changing the path or file name in the Restore As column is equivalent to using the MOVE option in a Transact-SQL RESTORE statement.

  • Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
    Recovers the database. This option is equivalent to specifying WITH RECOVERY in a Transact-SQL RESTORE statement.

  • Leave the database non-operational, and don't roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
    Leaves the database in the restoring state. To recover the database, you will need to perform another restore using the preceding RESTORE WITH RECOVERY option (see above). This option is equivalent to specifying WITH NORECOVERY in a Transact-SQL RESTORE statement.

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

  • Leave the database in read-only mode. Roll back the uncommitted transactions, but save the rollback operation in a file so the recovery effects can be undone. (RESTORE WITH STANDBY)
    Leaves the database in a standby state. This option is equivalent to specifying WITH STANDBY in a Transact-SQL RESTORE statement.

    Choosing this option requires that you specify a standby file.

  • Rollback undo file
    Specify a standby file name in the Rollback undo file text box. This option is required if you leave the database in read-only mode (RESTORE WITH STANDBY).

Note

After you using the Copy Database Wizard to copy 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).