During my previous consulting engagement, I learned something new with SQL Server feature called back up to the URL. Since it was not so clearly documented, I am sharing this with you. In this blog post, let us learn about Azure Resource Manager. If you have not heard about this feature, you can think of it as variation of the SQL backup feature. In this type of backup, the destination would be an Azure storage rather than disk or tape. You can read more about this feature on my previous blog SQL SERVER – Backup to Azure Blob using SQL Server 2014 Management Studio.
My client was trying to take a backup on Azure, on a storage deployed under the ARM deployment model. You can read more about classic and ARM deployment models on below link.
Here is how they are seen on https://ms.portal.azure.com as of today.
Here is the error they were getting
Msg 3271, Level 16, State 1, Line 9
A nonrecoverable I/O error occurred on file “” Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request..
Msg 3013, Level 16, State 1, Line 9
BACKUP DATABASE is terminating abnormally.
I was able to reproduce the same error for ARM storage accounts (blob storage)
Solution / Workaround
If you are trying to take a backup to Azure blob storage in the ARM deployment model, then make sure the “kind” of storage account should be selected as “General” while creating storage account. If we choose “Blob Storage” then it would fail.
The below image shows the option to choose while creating it.
If you already have an account created, then you can check it via looking at the icon.
Have you faced this problem?
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Hi Pinal,
This is Xiaochen from Microsoft Azure SQL DB team. Thanks for your writing and letting us know this problem. I’m sorry for the inconvenience it caused.
If you are running SQL Server 2014, the backup to URL to backup the database or transaction log to page blob, which is not supported in Blob Storage accounts. The Blob Storage Accounts support only block and append blobs. You can find more details here: https://docs.microsoft.com/en-us/azure/storage/common/storage-quickstart-create-account . It will work with General-purpose Storage Accounts as you proposed.
In SQL Server 2016, we updated backup to URL feature to backup the database or transaction log to block blob. It will work well with Blob Storage accounts.
We will update the documentation for SQL Server 2014 and earlier version for this limitation. Please feel free to contact me if you have any further question. Thanks!
Regards,
Xiaochen
Still getting the issue on SQL2016
It’s Q4 2017 and I am having problems. Can we get an update on this?
The issue is still there and I am hard to find to use the General Purpose Storage. Soon I am trying to create a Container in the GP it’s failing.
Hi Pinal,
Found that this error can also occur due to TLS versions. If SQL server is using TLS 1.0, the storage account in Azure also should be enabled for TLS 1.0.