Backing Up A Database To URL

 

In a previous post, I talked about some of the available storage options that can be used to back up your databases directly to the cloud. Doing this is a really good way to get your backups off-site and replicated in a single motion, depending on the region and redundancy option selected, for minimal cost.

So how do you back up to a URL in the cloud? I’ll show you. Before we start, this assumes that you have a storage account in Azure setup and configured. If not, go do that first and then come back. You will also need SQL Server 2012 SP1 CU2 or higher in order to make this work. You will need SQL Server 2016 or higher if you want to backup to block blobs.

Before you start backing up to Azure, you will need to decide whether you want to use block or page blobs for your storage. As I mentioned in my previous post, using block blobs is recommended for cost as well as performance. However, you can use either.

With storage in Azure, the method used to back up the database will determine which storage type is used. In either case you must use a credential. If you use a credential with a Share Access Signature, it will be a block blob. If you use a credential with a storage account access key, it will be a page blob.

If you look on the blade for the storage in the Azure portal, you will see both an option for Access Keys as well as the Share Signature.

 

Page Blobs

Azure allows you to have two access keys which allow you to rotate keys as needed when using page blobs. You will need one of these keys in order to successfully back up using page blobs.

 

Like with any keys, you want to safeguard them. If someone has the keys and knows the storage account name, they could gain access. In short, rotate your keys regularly.

Once you have the key, you can create the CREDENTIAL.

CREATE CREDENTIAL [sqlbackups] WITH IDENTITY = 'backupsdemo'
,SECRET = '<mystorageaccountaccesskey>';

The identity value is the name of your storage account. The Secret is the key that you obtained from the portal. Once the credential has been created, you can then backup your database.

BACKUP DATABASE ScratchDemo
TO URL = 'https://backupsdemo blob.core.windows.net/sqlbackups/ScratchDemo_pageblog.bak'
     WITH CREDENTIAL = 'sqlbackups'
GO

Block Blobs

With block blobs, using the Shared Access Signature (SAS) is a little different. When you configure the signature, you will specify a time frame in which access will be allowed. You also need to specify an IP address or range. Once the signature has been created, you’ll see a bunch of options like what is shown below,

 

Keep in mind that when generating the shared access signature, it utilizes the access keys. You can specify either key1 or key2 for this process. If you regenerate either of those keys, you could need to regenerate the SAS token and then update. Once the SAS token is generated, a connection string, token, and various URL endpoints will be displayed like shown below.

 

In order to use the signature for backups, we need to copy the SAS token. We will use this token to create the corresponding credential. One thing to note, the SAS token will begin with a ‘?’ which SQL Server already expects. When you create the credential, remove the ‘?’ from the token.

CREATE CREDENTIAL [https://backupsdemo.blob.core.windows.net/sqlbackups]
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
  SECRET = '<SAS_TOKEN_GOES_HERE>';

The credential name needs to be the URL that points to your storage account name and subsequent container. The “Secret” is the SAS token that we gathered from the portal after generating the shared access signature (SAS).

BACKUP DATABASE ScratchDemo
TO URL = 'https://backupsdemo.blob.core.windows.net/sqlbackups/ScratchDemo_blockblob.bak'

Note that it is completely possible to have a credential for both types of storage (block or page) created. The page blobs credential can be named anything, however the block blob credential must to be named the URL of the storage endpoint including the container name.

 

Verification

Once the database has been backed up you can see the files in the Azure portal.

 

Note that I have these stored in Cool storage so unless I want to pay a penalty for early deletion, they will sit there for at least 30 days. In a future post, I’ll show you how you can remove the files using Powershell. If you are using Ola Hallengrens maintenance solution (which we recommend) it fully supports backing up to URL.

Summary

Ensuring your backups are safe and sound is a primary tenant of being a DBA. Backing up to URL is a great way to accomplish this. For a minimal cost, your backups could be off-site or even redundant across the country. Keep in mind, however, that even with Azure things fail. If and when your internet connectivity drops, make sure that you have a backup plan to handle temporary onsite backups until connectivity is restored.

Happy backing up!