Restoring an Expressmaint backup using SQL Server Management Studio Express

This post describes step by step how to restore a full database backup taken with Expressmaint (or by any other means) using SQL 2005 Management Studio Express (SSMS). The same steps apply to SQL 2008. To download this free tool for SQL Server Express Edition use the links below

  1. Open SSMS (Start>Programs>Microsoft SQL Server 2005>SQL Server Management Studio Express)

  2. Connect to your SQL Server instance (in this example I am connection to a named instance called SQLExpress using .\SQLExpress as the server name). The default for a SQL Express install is to create a named instance called SQLExpress



  3. By default this will open up Object Explorer in the left pane of SSMS but if not click on View>Object Explorer from the menu or press F8

  4. Right click on the Databases folder in Object Explorer and choose Restore Database from the menu as shown below



  5. This will open the General tab of the Restore Database dialog window. Type in the name of the database you want to restore (in this example DB1). Select the From device radio button and click on the file browse button as shown below



  6. This will bring up the Specify Backup dialog that lets you select the specific backup file you want to restore. Click on the Add button to bring up the Locate Backup file dialog and browse to the backup file. In this example Expressmaint has been used to backup the DB1 database to C:\Backups\DB1 as shown below. Click on the file you want to restore and click OK



  7. The selected backup file will now be selected (as shown below) so click OK on the Specify Backup dialog to return to the Restore Database dialog as shown below



  8. On the Restore Database dialog select the checkbox in the Restore column in the lower part of the dialog next to your selected backup as shown below and then click on the Options tab in the upper left corner of the window as shown below



  9. On the Options tab review the database file locations to confirm they are correct and leave the Recovery State radio button on the default selection (RESTORE WITH RECOVERY) as shown below and then click OK to restore the database



  10. You should then get a pop up dialog box confirming that the restore has completed successfully as shown below. Click OK to close the Restore Database dialog. Congratulations the database has been restored! If you get an error see the troubleshooting section below.

 

Troubleshooting

The most common error you will see when restoring a database is shown below and is caused by there being connection to the database you are trying to restore. In order to restore an existing database there must be no user connections to that database.

As this error indicates, exclusive access is required to the database in order to restore a backup "over the top of it". Make sure any applications that use the database are switched off. To determine what connections there are to a database open a New Query window in SSMS (click on the New Query button in the top left of the tool) and run the following query (replace DB1 with the name of the database you are restoring) by clicking on the Execute button in the toolbar (or pressing F5)

use master
go
select
* from sys.sysprocesses where dbid = db_id('DB1')

In the example below there is just 1 session with id = 52 connected to the DB1 database

I can then go ahead and close this connection by using the KILL command which takes a session id (spid) as a parameter so in this example I would issue the following command in my SSMS query window and click on Execute (or press F5)

KILL 52

If I then rerun the first query there are no results indicating that there are no user connections to the DB1 database and I can proceed with the restore. If there are multiple connections to the database then you can repeate the KILL command with the relavent session id's.

Published 21 October 2009 18:08 by sqldbatips

Comments

30 November 2009 18:21 by OPI's SQL Blog

# Newsletter PASS Deutschland e.V. Ausgabe Dezember 2009

unseren neuen monatlichen Newsletter gibt es hier zum Herunterladen: www.sqlpass.de/.../Newsletter_2009_12.pdf ...