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.