SQL Server 2014 Backup to URL

I’m absolutely in love with the concept of being able to create a backup directly to a protected, off-site location. Yeah, you can spend all sorts of money on terribly wonderful technology to enable that within your enterprise. And if you have that kind of money, great. But, what if you’re like most everyone else and you just want a little more protection without mortgaging the house? Let’s take a look at one possibility, backup to URL.

There have been ways to backup to hosted storage, whether it was DropBox, AWS or Azure blob storage, for quite a while. But, every method I tried out involved setting up some type of drive on your system. As soon as you had your K:\ drive mapped out to AWS or whatever, you could run a SQL Server backup directly to that location. Done. But, I’ve long wanted to just do it straight. Just to backup right to that location, no additional spoofing required. As long as you’re willing to keep your stuff within Azure blob storage, it’s now possible. Further, within SQL Server 2014 you can do it directly from T-SQL, no additional application required (there is an app for 2005, the 2008s and 2012). How is easy. Let’s talk about why for a minute.

Backups are not a technological problem. Oh, I know they can both literally and figuratively keep us nerds up at night, but the fundamental definition of the backup is something that the business must provide. They need to tell us how much data to risk, how long we can spend on recovery through things such as the Recovery Point Objective and Recovery Time Objective. I don’t want to get too deep in the weeds defining these terms. What I do want to point out is that your disk is at risk. No, not the one that’s storing your database (although, it’s at risk too). I’m talking about the one storing your backups. That thing is just waiting until the most opportune moment to die. And by opportune I mean of course, when it will be the hugest pain for you. Plus, are you in a flood zone? A wind zone? Earthquakes? Fire? Oh yeah, the gods and the universe well and truly hate your orderly little backup disk. So, it’s a good idea to have a second location for those backups. Back in the day, we paid a company to take our tapes to an off-site storage. Now, you can just go to a hosted storage site like, oh, I don’t know, Azure blob storage.

Here’s how it works. You have to have already set up an Azure account. You should also create a container to keep your backups in(if you need help dealing with blob storage, check out Cerebrata). While you’re there, make sure you get the URL for your storage location and the Access Key for your storage. Now, let’s go back to T-SQL. First, we need to create a CREDENTIAL. This gets us onto our URL without having to paste sensitive information into our backup processes:

CREATE CREDENTIAL MyCredentialName
WITH IDENTITY = 'MyStorageAccountName',
SECRET = 'MyAccessKey';

With that done, the rest is easy:

BACKUP DATABASE MyNewDB TO  
URL = N'http://myserver.blob.core.windows.net/scarybu/MyNewDB.bak' 
WITH  CREDENTIAL = N'MyCredentialName', 
NAME = N'MyNewDB-Full Database Backup', STATS = 10;

The syntax is pretty standard. You’re going to BACKUP a DATABASE named MyNewDB to your URL. In the URL, make sure you supply, not just the URL to your blob storage account, but the container and the backup file name. My container is ‘scarybu’ and the file name is ‘MyNewDB.bak.’ In the WITH clause you’ll specify the CREDENTIAL that you created earlier.

Yes. It’s that easy. Restore is the same thing only in reverse.

Now you can protect your backups by ensuring that you also put a copy to an offsite location and you can do it directly from with your area of expertise and control, SQL Server and Management Studio. For more details refer back to the Books Online.