Published
Mar 23 2019 06:32 PM
1,728
Views
First published on MSDN on Oct 17, 2017
SQL Server 2012+ and Azure SQL Managed Instance support native BACKUP commands that can backup a database to Azure Blob Storage URL. Setting-up Azure Storage account might not be so easy as you think because there are some constraints.
You can learn about backup to Azure storage here , but there are some hidden constraints that might hit you. In this post, I will show you how to properly setup Azure Blob Storage before you start backups.
BACKUP TO URL can backup database to Classic Standard storage account. If you choose Resource Manager 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 Blob Storage if it is in the "Resource Manager Deployment" model. Therefore, make sure that you choose the following settings when you create Azure Storage:
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 SQL Database 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 Blob Storage and let SSMS to create CREDENTIAL.
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.
SQL Server 2012+ and Azure SQL Managed Instance support native BACKUP commands that can backup a database to Azure Blob Storage URL. Setting-up Azure Storage account might not be so easy as you think because there are some constraints.
You can learn about backup to Azure storage here , but there are some hidden constraints that might hit you. In this post, I will show you how to properly setup Azure Blob Storage before you start backups.
Use Standard Classic storage account
BACKUP TO URL can backup database to Classic Standard storage account. If you choose Resource Manager 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 Blob Storage 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 SQL Database 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 Blob Storage 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.