Restoring From A Database Back Up

Good afternoon everyone!

Yesterday, I wrote a short blog post covering how to create a back up from your database in order to safeguard yourself from losing your valuable data. Today, I am continuing with this theme and going to discuss how to restore your database from a backed up file. If you haven't done so already, I would suggest reading yesterday's blog post, which can be found here:

http://sql-genius.blogspot.co.uk/2013/07/creating-database-backups.html

Restoring From a Database Back Up 

In order to restore from a back up, right click on your database instance and go to Tasks > Restore...
As you can see below, there are 4 different options that you can restore from:

• Database
• Files and Filegroups
• Transaction Log 
• Page

For the purpose of this example, I am going to do a complete restore from a database back up (this is simply because it is the method I have used most often in my work place). For information on other types of back up, take a look at the link below and this will give you a more informed choice on what kind of restore is most suitable for your needs:

http://msdn.microsoft.com/en-us/library/ms191253.aspx

After clicking on "Database...", you should see the following screen: 

General 

Now you will need to decide where to back up the database from. Again, for the purposes of this tutorial I am going to use the back up created in yesterday's blog post. Therefore, I want to select "Device" and click on the ellipses (...) as seen in the image below:

Hopefully you will remember where you stored your database back up. Always make sure that the file has been named properly and is stored within an accessible folder on an appropriate medium. Note that it's not always a good idea to have a back up on the same device as the main database because if something goes wrong with that server, not only would you have lost the primary instance but you would have lost the back up instance too. If you can't find your file straight away, make sure you have "view all files" on as SQL Server may not pick up the .bak file straight away. Once you have found the back up, click on add:

Once you have made your decisions on this page, always make sure any other options are set up correctly as well.

Files 

Alternatively to restoring the entire database from a back up, you can select specific files to back up from. However for the purpose of this blog post, I can skip this part.

Options 

You can choose different options to achieve different results:

• Overwrite the existing database (WITH REPLACE)
• Preserve the replication settings (WITH KEEP_REPLICATION) 
• Restrict access to the restored database (WITH RESTRICTED_USER) 

(Note that none of the options listed above are actually compulsory and you can simply leave the boxes blank or uncheck any checked boxes).

or 

• RESTORE WITH RECOVERY - (default) - Any uncommitted transactions are rolled back so the database will be in a usable state after the restore. 
• RESTORE WITH NONRECOVERY - Any uncommitted transactions are left in the state they were in before the restore. This means that if you wish to start using the database again after the restore, you will need to cover the database first. 
• RESTORE WITH STANDBY - Any uncommitted transactions are rolled back but the database is left in a read-only mode. 

For the purpose of this example, I want to overwrite the existing database and it's contents and leave the RESTORE WITH RECOVERY default on as well. This is the set up I have used when restoring databases in my working environment:

Once you are satisfied that you have set all of the options correctly, click on OK. The progress bar in the bottom left will turn and there will be a restoration bar along the top of the window as well. 

Once the restore is complete you will see the following message: 

Now, if you select the database and query from it, you will notice that it's been completely restored back to its previous state (when the back up was created).

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.