Backing Up Your SQL Server Database to Windows Azure Blob Storage

So recently, I was speaking at my first SQL Saturday of 2014 in Nashville (great job by Tammy Clark (b|t) and her team to put on a great event!) and due to a weird confluence of circumstances, I couldn’t get to my laptop (it involves my car being in the shop, an emergency performance tuning engagement snow, and not being about to get into my office.)

So, I thought, no fear, I have my trusty Surface Pro and a MSDN account—so I fired up a couple of virtual machines running SQL Server 2012 and 2014. The databases I use for my Data Compression and Columnstore presentations are Adventure Works, but I do a lot of prep work to build some big tables to make the demos be more dramatic. Unfortunately, when I started down this performance was pretty slow—something I’m likely attributing to thin provisioning on the Windows Azure Infrastructure side.

Since both databases (2012 and 2014) are the same—I thought let me just do a backup and restore, especially since I didn’t need to actually do work—I just had to fire up a restore. Additionally, I wanted to learn how to do something new.

First Things First

So I’m going to go on limb and assume that you have a Windows Azure account. From there you’ll need to create a storage container.

 

You want to make note of a couple of things in your storage account. I added a new storage account called ‘sqlbackupsjd’, and then I created a container a container called ‘mycontainer.’ You will want to make note of the URL associated with this container. We will need it when we get back to SQL Server.

Also, you’ll want to click ‘Manage Access Keys’ and grab the primary access key.

 

Moving on to SQL Server

You’ll need to be on SQL Server 2012 SP1 CU2 or forward to take advantage of this feature. The first thing we will need to do is create a credential to be able to pass our security information to Windows Azure.

So in my case, ‘mystorageaccount’ is ‘sqlbackupsjd’ and the secret is my storage access key. From here—backing up the database is really straightforward.

 

Restoring is just the opposite of the process. On the target server we create the credential, and then do the restore.

 

Summary

Cloud backup is one of my favorite use cases for Azure services. Much like DR, many organizations have a requirement to have offsite backups. This process can be an easy way to automate this process, without worrying about handing off your tapes to someone else. (Or storing them in the DBA’s wine cellar). It’s a really easy process with SQL 2012.

 

About jdanton1
A DBA, cyclist, cook (who likes to play chef occasionally)

2 Responses to Backing Up Your SQL Server Database to Windows Azure Blob Storage

  1. Jai Prakash Chauhan says:

    So, in this case will the data transfer charges is applicable (because data is moving out from data center to our local box)?

  2. jdanton1 says:

    The backup (inbound) is free–if you restore to a server outside of the Azure data center to where your backups are there is free. One DR strategy people have used is to backup to Azure, and spin up a SQL VM to restore the DB to..

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

%d bloggers like this: