Create a New Database From an Existing Database Backup (SQL Server Management Studio)

After you connect to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

Right-click Databases, point to Restore, and then click Database. Clicking Database opens the Restore Database dialog box.

On the General page, the name of the source database appears in the To database list by default. To create the new database, enter its name in the list box.

In the To a point in time text box, either use the default (Most recent possible) or click the browse button to select a specific date and time.

To specify the source and location of the backup sets to restore, click one of the following options:

• From database

  Enter a database name in the list box.

• From device

  Click the browse button. This opens the Specify Backup dialog box. In the Backup media list, select one of the listed device types. To select one or more devices for the Backup location list, click Add.

  After you add the device or devices you want to the Backup location list, click OK to return to the General page.

In the Select the backup sets to restore grid, select the backups to restore. This grid displays the backups available for the specified location. By default, a recovery plan is suggested. To override the suggested recovery plan, you can change the selections in the grid. Backups that depend on the restoration of an earlier backup are automatically deselected when the earlier backup is deselected.

For information about the columns in the Select the backup sets to restore grid, see Restore Database (General Page).

To view or select the advanced options, click Options in the Select a page pane.

In the Restore options panel, optionally select any of the options:

• Overwrite the existing database

• Preserve the replication settings

• Prompt before restoring each backup

• Restrict access to the restored database

For complete descriptions of theses options of the Restore options panel, see Restore Database (Options Page).

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

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 file name that will be used as the restore destination for this data file or log file. To move the database that you are restoring to a different location, edit the Restore As field of each file and specify the new destination path or file name, or both. Also, if you want to restore a copy of a database on the server instance while the original database is still attached, you must specify a new database name in the To database field on the General page of the Restore Database dialog box. Note Changing the restore destinations in the Restore As column is equivalent to using the MOVE option in RESTORE statements.

The Recovery state panel determines the state of the database after the restore operation. The default behavior is to:

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

  Note
  Choose this option only if you are restoring all the necessary backups now.

Alternatively, you can select to either:

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

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

For descriptions of the options of the Restore options panel, see Restore Database (Options Page).