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
Open SSMS (Start>Programs>Microsoft SQL Server 2005>SQL Server Management Studio Express)
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
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
Right click on the Databases folder in Object Explorer and choose Restore Database from the menu as shown below
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
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
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
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
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
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.
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)
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)
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.