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.

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 backup your SQL Server database to the file system.  Here are some key reasons why you may want to backup a database:

• To create historical copies of your database schema and data.  In case your system crashes or you mess up the data/table definitions, you can create periodic backups to file and restore them later in the event of such an emergency.  A good example of this scenario is if you messed up some important table definitions and cannot undo your changes.  Another example is if the data in the tables gets erased accidentally.
• To give your database to somebody else so that they can use it.  If you are collaborating with somebody, it comes in handy to be able to provide them a database backup file so that they can restore it into their SQL Server and they will be able to see all of your tables and data.
• To deploy the database in another development environment, like on a test system.  Software projects frequently take a snapshot of a development database by using a backup and deploy the database file to another SQL Server by using the Restore Database option.

First, logon to SQL Server Management Studio and expand the Databases list.  Right click on the database that you want to backup and choose “Tasks->Back Up…”.  A popup window called Back Up Database will appear with a bunch of options that you can configure for the backup operation.

Make sure that “Backup type: Full” and “Back up to: Disk” are chosen.  The exact backup file location will vary depending on your exact SQL Server version but it will look something similar to ”C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\shoppingcart_CSharpUniversity.bak”.  This folder is the default SQL Server backup folder.  The default file name for the backup is the name of your database, appended with .bak.

When you are ready to start the backup, click on the OK button and SQL Server will starting writing out the backup file.  When it has completed, it will display a message.  After that occurs, you can access the output file by opening Windows Explorer and navigating to the backup location that was listed in the Back Up Database window.  You can copy the .bak file somewhere else or do whatever you need with it.  You can provide this .bak file to somebody that you are collaborating with and they should be able to restore the database into their SQL Server.  *If they have problems restoring your database backup, check to see if the two versions of SQL Server (yours versus theirs) are not compatible with each other.

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

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