How to: Restore a Database Backup (SQL Server Management Studio)
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 database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. Without the certificate or asymmetric key, the database cannot be restored. As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed. For more information, see SQL Server Certificates and Asymmetric Keys.
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).
Note
After you restore 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, if a full-text catalog is not available, the associated full-text indexes are rebuilt. 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).
See Also