Restoring your Sql Server Database

In a previous posting, we backed up your WideWorldImporters database and created a job to run transaction log backups so we can recover with no more than 30 minutes of data loss.

We will discuss the most important task of being a DBA and that is restoring your backups.  A backup is worthless if you cannot bring the data back.

Below, I created a table at 4:09pm called jim_invoices from the Sales.Invoices table, to show how we can recover the data after the full backup was made.

restore1

 

The full backup (below) was made at 2:15pm, and the transaction logs have been backing up every half hour.  After I created the table jim_invoices above, I kick off the transaction log backup job that we created in last weeks posting at 4:11pm so the data will get backed up.  This means if the database got corrupt, or somebody deleted the database (?!), or if the server blows up, we can recover those changes.

restore2

 

Oh no, one of the DBA’s deleted the WideWorldImporters database on the production server instead of development while trying to refresh.  Do not panic, we have our backups and know how to recover the data.  We will have downtime, and probably an unpleasant discussion with management, but let us get the data restored.

As with everything with Sql Server, there are many ways to do restores of your data.  I will show you how to do it from SSMS using the wizard.  A lot of people also like to script their restores.

 

Restoring your backups.

Right click on Databases in object explorer and click: Restore Database.

restore3

 

This brings up the Restore Database wizard.  Our files are saved on disk, so select the Device radio button and then click the ellipses to the right.  This brings up the select backup devices prompt.  Click Add and navigate to the directory where your backups are stored (remember, they should be stored in multiple locations so there is no chance of losing them).

 

restore4

 

After you select the files, click OK.

 

restore5

 

Then click OK again.

 

restore6

This will read through the files and show you a synopsis of the files restoring and what time it can restore to.  On the General page, according to the full backup and transaction logs we provided, we can restore to 4:11pm which was the last log we had a backup of.

restore7

 

If you click on the Timeline button, you can play with the slide bar and restore to different times since we have all of the transaction logs since the full backup.  However, we are going to restore to 4:11pm.

restore8

 

On the Files page, this allows you to change the directory you want to restore to.  You do this if the original drive no longer exists, or if you are restoring to another server that does not have the same drive structure or if you just want to restore to a new location.

restore9

 

The Options page has many items that can be used, and rather than explain them all in this posting, I will point you to Books Online  https://msdn.microsoft.com/en-us/library/ms188223.aspx.  Every restore scenario has different requirements, but for ours that we are doing, the recovery state should be: RESTORE WITH RECOVERY.

This will use all of the transaction logs that we provided and will not allow any others to be applied.  This will then open the database.

 

restore10

 

Click ok when you are happy with your selections and the recovery process will start.

 

restore11

 

Depending on how big your database is and how many transaction logs you are restoring, you will eventually get the database restored successfully message.

restore12

 

If we query the database, we can see the jim_invoices table has been restored and the database has been restored to 4:11pm

restore13

 

You have just restored your database and the business can now continue to operate.

Restoring your backups should be done as often as you can onto test servers or in development.  This keeps your skills sharp and you will have confidence when you need to perform this critical task under the gun and most importantly, it validates that your backups actually work.

One thought on “Restoring your Sql Server Database”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s