Home Page

By using the SQL Server Management Studio that installs with SQL Server you can perform a number of database administration tasks.  In this lesson I will show you how to restore (import) a SQL Server database from the file system.  Here are some key reasons why you may want to restore a database:

  • Something went wrong with your database like data was erased inadvertently or the schema was somehow altered in a bad way.  Restoring a good recent backup file will get your database back in working order.
  • Somebody has provided you a backup file and you want to import their database onto your SQL Server.  For example if you purchased a book that has a database with example tables in it and you want to import it to be able to see the database.
  • You are trying to copy a database to a deployment environment.  For example, you want to create a test database on a separate server that looks just like the development database.

Before I start this lesson I want to talk about something that you should do before you attempt to restore a database in order to make your life easier.  You should copy the database backup file (with .bak extension) that you are trying to restore into your SQL Server’s default backup folder.  For me I copied the backup file shoppingcart_CSharpUniversity.bak into the folder “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\”.  The exact folder of your SQL Server’s default backup location may vary depending on your exact SQL Server version but it will be something similar.  By copying the backup file to this location, it will make it easier to perform the restore because SQL Server has the security access rights to read from that folder.  I have noticed that sometimes people try to restore backup files from their Desktop or My Documents folder and it ends up throwing an error because of security access.

First, logon to SQL Server Management Studio and expand the Databases list.  Right click Databases and choose “Restore Database…”.  A popup window called Restore Database will appear with a bunch of options that you can configure for the restore operation.  There are some options that you have to set in order for the restore to work successfully.  I will cover those here.

The first option you have to set is click the “From device” radio button.  Then click on the “…” button next to From device and it will bring up another popup window called “Specify Backup”.  In the Specify Backup window choose File from the drop down box and click Add to go and choose the file from the file system.  Choose the file that you copied into the SQL Server default backup location earlier in this lesson.

Once you have chosen the file and are back in the Restore Database window, make sure to click on the checkbox next to the new backup set that you just added.  Note: backup sets are listed under “Select the backup sets to restore”.

Finally, in the “To database” field near the top of the window, type in the name of the database that you want to create.  This name is important if you are trying to get an example C# ASP.NET working and the example code expects a certain database name.  For my example shopping cart ASP.NET application you should set the “To database” to “shoppingcart_CSharpUniversity”.  Click on the OK button to start the restore operation.

If you expand the Databases in Management Studio, you should now see the new database that you imported in the list.  Open up that database and verify that the tables were created if you want to be sure that everything was restored successfully.  If you are trying to get an example ASP.NET C# program working with the database, you may want to run that application and make sure that it can connect to the newly imported database.

Click here to watch a video example of how to restore a SQL Server database from file.

To read a related lesson that shows you how to backup a SQL Server database to file click here.


  1. Thank’s …. restore sql server 2005
    it’s working

    Comment by — May 6, 2009 @ 2:24 am

  2. I followed the steps above, and I get this error. What happened? Can anyone help me? I`m running Windows Vista Ultimate x86 in Administrator mode, so I have privileges.


    Restore failed for Server ‘PRESARIO-PC\SQLEXPRESS’. (Microsoft.SqlServer.Express.Smo)

    Program Location:

    at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
    at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()


    System.Data.SqlClient.SqlError: The operating system returned the error ‘5(error not found)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\agenda.mdf’. (Microsoft.SqlServer.Express.Smo)

    For help, click:

    Program Location:

    at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
    at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
    at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)

    Comment by Mad_Dog — May 21, 2009 @ 10:02 am

  3. Mad_Dog,
    In order to fix this error “5(error not found)” when restoring an SQL Server database, you need to grant Write permission on the target folder to the computer Account or Group that SQL Server is running as. For you, it looks like the folder you are trying to restore the database to is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL so then try the following steps:

    1) Determine what account your SQL Server is running as. Open Control Panel and go to Services. Find the service named SQL Server or SQL Server (SQLEXPRESS) and open it up. Go to the Log On tab and see what account it is using. For me this is “Sql Server Service”.

    2) Open Windows Explorer and determine the folder where your database files (*.mdf) are located). For you the path above is likely where they are. For others, this location depends on what version of SQL Server you are running so each computer will be different. For example on my computer the path is C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA

    3) Right click on the folder where your database files are and choose Properties. Go to the Security tab and grant Full Control to the account you found in Step 1 above.

    Comment by ted — May 22, 2009 @ 8:25 am

  4. Hello Ted,

    These instructions helped me immensely. I think my issue is now resolved.

    Thank You,


    Comment by Amish Parekh — July 31, 2009 @ 7:25 pm

  5. Hello,Can u tel me why i am not able to view the other pages,as i navigate to those links…???

    Comment by Neha — January 13, 2010 @ 1:18 am

  6. Thanks Ted

    Comment by Denis — May 8, 2010 @ 4:16 am

RSS feed for comments on this post. TrackBack URL

Leave a comment