Configure Azure Storage for SQL Database backups

First published on MSDN on Oct 17, 2017

2012+ and Azure SQL Managed Instance support native BACKUP commands that can backup a database to Azure Blob URL. Setting-up Azure account might not be so easy as you think because there are some constraints.

You can learn about backup to Azure

here

, but there are some hidden constraints that might hit you. In this post, I will show you properly setup Azure before you start backups.

Use Standard Classic storage account

BACKUP TO URL can backup database to
Classic Standard
storage account. If you choose model or premium storage type, you will probably get something like the following error when you start backup:

Msg 3201, Level 16, State 1, Line 7

Cannot open backup device ‘https://managedinstance.blob.core.windows.net/backups/tpcc.bak‘. Operating system error 50(The request is not supported.).

Msg 3013, Level 16, State 1, Line 7

BACKUP DATABASE is terminating abnormally.

Also,

Connect to a Microsoft Azure Subscription

will not work in a if it is in the “Resource Manager Deployment” model. Therefore, make sure that you choose the following settings when you create Azure Storage:

Credentials

Once you setup storage account and create container where you will place your backups, you need to create a CREDENTIAL in master database that will be used to backup database. Credential MUST have the same name as Azure Storage URL:

CREATE CREDENTIAL [https://managedinstance.blob.core.windows.net/backups]

WITH IDENTITY='SHARED ACCESS SIGNATURE'

, SECRET = N'sv=2017-04-17&ss=bfqt&srt=sco&sp=rwdlacup&se=2018-10-18T02:51:12Z&st=2017-10-15T18:51:12Z&spr=https&sig=7lxfhiQNAt%2F%2Bo%3D'

One thing that will break this is value that you put in the SECRET! SECRET is SAS key of Storage account that you can find on a portal. You can generate new SAS key if you go to Shared Access Signature blade and generate SAS:


Note another interesting thing – UTC time in drop down. Azure portal will place your current time in Start Time, but it will leave UTC time zone instead of your timezone. If you are not in UTC timezone and you don't notice that this UTC time is actually in future, you might waist a few hours debugging why you cannot access Storage with properly generated token.

Finally, when you press Generate SAS you will get the valid SAS key that you can copy in your CREATE CREDENTIAL command. However, on the portal is shown value that is starting with “?”. If you just copy paste this string you backup will break, because Engine
expects a value without?
Make sure that you remove ? from SAS key if you copy this value.

Another option is to use SSMS to access and let SSMS to create CREDENTIAL.

Conclusion

These are probably three thing that can cost you a few hours of debugging if you are not creating Azure Storage keys every day. Make sure that you check type of storage, validity period, and ? in SAS key when you configure Azure Storage, or your backups will fail.

 

This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.