How to: Restore a Database Backup (SQL Server Management Studio)

Artikel
12/03/2008

This topic explains how to restore a full database backup.

Important

Under the full or bulk-logged recovery model, before you can restore a database in SQL Server Management Studio, you must back up the active transaction log (known as the tail of the log). For more information, see How to: Back Up a Transaction Log (SQL Server Management Studio).

To restore a full database backup

After you connect to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
Expand Databases. Depending on the database, either select a user database or expand System Databases, and then select a system database.
Right-click the database, point to Tasks, and then click Restore.
Click Database, which opens the Restore Database dialog box.
On the General page, the name of the restoring database appears in the To database list box. To create a new database, enter its name in the list box.
In the To a point in time text box, either retain the default (Most recent possible) or select a specific date and time by clicking the browse button, which opens the Point in Time Restore dialog box. For more information, see How to: Restore to a Point in Time (SQL Server Management Studio).
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, which opens the Specify Backup dialog box. In the Backup media list box, select one of the listed device types. To select one or more devices for the Backup location list box, click Add.
  After you add the devices you want to the Backup location list box, 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. Any backups that depend on a deselected backup are deselected automatically.

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, you can choose any of the following options, if appropriate for your situation:
- Overwrite the existing database
- Preserve the replication settings
- Prompt before restoring each backup
- Restrict access to the restored database
For more information about these options, see Restore Database (Options Page).
Optionally, you can restore the database to a new location by specifying a new restore destination for each file in the Restore the database files as grid. For more information about this grid, see Restore Database (Options Page).
The Recovery state panel determines the state of the database after the restore operation. The default behavior is:
- Leave the database ready to 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 of the necessary backups now.
Alternatively, you can choose either of the following options:
- 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, see Restore Database (Options Page).

Delen via

How to: Restore a Database Backup (SQL Server Management Studio)

To restore a full database backup

See Also

Tasks

Other Resources

Help and Information

Aanvullende resources