SQL SERVER – Backup to URL Fails in Azure Resource Manager (ARM) Deployment Model with Error – (400) Bad Request

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.

Azure Resource Manager vs. classic deployment: Understand deployment models and the state of your resources

Here is how they are seen on https://ms.portal.azure.com  as of today.

SQL SERVER - Backup to URL Fails in Azure Resource Manager (ARM) Deployment Model with Error - (400) Bad Request b2url-01

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.

SQL SERVER - Backup to URL Fails in Azure Resource Manager (ARM) Deployment Model with Error - (400) Bad Request b2url-02

SQL SERVER - Backup to URL Fails in Azure Resource Manager (ARM) Deployment Model with Error - (400) Bad Request b2url-03

If you already have an account created, then you can check it via looking at the icon.

SQL SERVER - Backup to URL Fails in Azure Resource Manager (ARM) Deployment Model with Error - (400) Bad Request b2url-04

Have you faced this problem?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – False Error – Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied.)
Next Post
SQL SERVER – SSMS Error During Restore: No Backupset Selected to be Restored

Related Posts

4 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

    Reply
  • Still getting the issue on SQL2016

    Reply
  • It’s Q4 2017 and I am having problems. Can we get an update on this?

    Reply
  • 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.

    Reply

Leave a Reply

Menu