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.

Good afternoon everyone! I hope you all had a lovely weekend!

Today's short blog post is going to cover how to create a back up from your database. You should always back up any active database on your server because it will safeguard your database (and yourself!) from losing any of your database and contents. 

Once you have created a back up, you can then restore completely (or even partially) to this back up. Sometimes, it's useful to use these back ups as a way of archiving old data. You never know when you may need to refer back to an archived application build and you will need the relevant database to go with it (as you know, databases can grow vastly in a very small period of time, from both a data point of view as well as database structure point of view). 

Creating a Full Database Back Up 

In order to create a back up, right click on your database instance and go to Tasks > Back Up...

You should see the following screen: 

Make sure that where you wish to back your data up to is the right place, and make sure that the file has a suitable name so you understand exactly what database has been backed up and when it was backed up. You can choose between 3 main back up types from the drop down list: 

• Full 
• Differential 
• Transaction Log

However different settings will provide an even wider range of back up types. Please see the link below for more details on them: 

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

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

For this example, I am just going to leave the default options. You can choose to overwrite any existing back up sets and set up new media sets to save your database back up to, however for this example I won't need to do that. 

Once you are ready to go, press ok and you will see this appear in the progress bar: 

If, for whatever reason, you want to stop the back up, click on the "Stop action now" hyperlink. This will cause no negative effects to the database at all. 

Once the back up is complete you will see the following message: 

What Next...

And that really is all there is to it! Note that you can also create timed regular back ups and this example just shows how you can do it manually. Tomorrow I will be discussing how to restore from a database back up so stay tuned for that! 

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.