October 2009 - Posts

Along with 53 other MVP's I've contributed a chapter to the SQL Server MVP Deep Dives book which is now available for preorder. All author royalties, along with a contribution from Manning Publications, go to support War Child International (visit www.warchild.org for more information). To order go to www.SQLServerMVPDeepDives.com and use discount code sqldeep40 for 40% off!


This is not an ordinary SQL Server Book. SQL Server MVP Deep Dives brings together the world's most highly-regarded SQL Server experts to create a masterful collection of tips, techniques, and experience-driven best practices for SQL Server development and administration. These SQL Server MVPs—53 in all—each selected a topic of great interest to them, and in this unique book, they share their knowledge and passion with you.

SQL Server MVP Deep Dives is organized into five parts: Design and Architecture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. Within each part, you'll find a collection of brilliantly concise and focused chapters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance. The range of subjects covered is comprehensive, from database design tips to data profiling strategies for BI.

Posted by sqldbatips | with no comments
Filed under: , ,

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.



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
* 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.