How to: Restore Files and Filegroups (SQL Server Management Studio)
This topic explains how to restore a full file backup.
Important
Under the full or bulk-logged recovery model, before you can restore files 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 file 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 Files and Filegroups, which opens the Restore Files and Filegroups dialog box.
On the General page, the name of the restoring database appears in the To database list box.
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.
- From database
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 Files and Filegroups (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
- Prompt before restoring each backup
- Restrict access to the restored database
For more information about these options, see Restore Files and Filegroups (Options Page).
Optionally, you can restore the database to a new location by specifying a new restore destination for each file in the Restore database files as grid. For more information about this grid, see Restore Files and Filegroups (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 select either of the following options:
- Leave the database non-operational, and don't roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
- Leave the database in read-only mode. Roll back the uncommitted transactions but save the rollback operations in a file so the recovery effects can be undone. (RESTORE WITH STANDBY)
For descriptions of the options, see Restore Files and Filegroups (Options Page).
See Also
Tasks
How to: Back Up Database Files and Filegroups (SQL Server Management Studio)
How to: Back Up a Database (SQL Server Management Studio)
How to: Back Up a Transaction Log (SQL Server Management Studio)
How to: Restore a Transaction Log Backup (SQL Server Management Studio)
Other Resources
RESTORE (Transact-SQL)
SQL Server Management Studio Tutorial