Share via


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.

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

See Also

Other Resources

Differential File Backups
Full File Backups
How to: Back Up Database Files and Filegroups (SQL Server Management Studio)
How to: Restore Files and Filegroups (Transact-SQL)
How to: Restore Files and Filegroups over Existing Files (Transact-SQL)
How to: Restore Files to a New Location (Transact-SQL)
Performing File Restores (Full Recovery Model)
Performing File Restores (Simple Recovery Model)

Help and Information

Getting SQL Server 2005 Assistance