Azure – SQL Server 2016 Web Backup to URL

azurebackupsql-server-2016

I recently stood up a new Windows Server 2012 R2 instance with SQL Server 2016 Web Edition and am trying to setup automated backups to Azure, but not having any luck. I created the storage account on Azure first, then created the Shared Access Signature (SAS) and that's where I start to get lost. From here, I've tried:

  • Creating local credential using this script:

    CREATE CREDENTIAL [BlobUrlWithContainerName]
WITH IDENTITY = '[FriendlyName]', 
SECRET = '[SAS Token]'`

    No errors were reported with the above script, however issuing a BACKUP DATABASE command to a URL results in a not supported command (full error is: Cannot open backup device 'https://[storagename].blob.core.windows.net/[containername]/AdventureWorks2016.bak'. Operating system error 50(The request is not supported.).

  • I then tried going through SSMS on an individual database backup option, with right-clicking on a database, selecting Tasks ==> Back Up.., changing Back Up To to a URL and clicking Add. When I do that, I get prompted with this:

    SQL Backup Prompt

    No registered containers exist (nor can I find any details on how to register one), so I click New Container which gives me a Connect to a Microsoft Subscription dialog. I signed in with my Azure account, see two subscriptions (Free Trial, which is expired, and Pay As You Go, which is the correct one), select Pay As You Go, and then when I go to select my storage account, the dialog closes and I get a "Index was out of range" error, which implies it can't find any containers. Here are the relevant screenshots:

    Microsoft Subscription Prompt

    Index out of range error

I'm at a loss for where to go from here. Any ideas?

Best Answer

Two backup options: BACKUP DB TO URL WITH CREDENTIAL - makes blob, requires credential to use token, not SAS, when creating [Cannot stripe backups across multiple files]

BACKUP DB TO URL [sans credential] - makes blocks, requires credential name to match the url [storage account + container], SAS in secret, [CAN stripe backups across multiple files]

Removing the leading '?' is hardly ever mentioned and stuffs everyone around.

Operating System error 50 (for me on Windows Server 2012 R2; sql server SQL Server 2016) is possible even when everything is correct: I have a job which backups dbs to Azure striping across files - failed 1 database on Friday, succeeded same db without changing anything on Saturday... just to add fuel to the fire.

It may be your authentication which is causing your failure to locate containers. 

   USE master  
   CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] -- this name must match the container path, start with https and must not contain a trailing forward slash.  
  WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.   
     , SECRET = 'sharedaccesssignature' –- this is the shared access signature token   
  GO

Source: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql.

/* Leaving this as historic context */ REFER "MSDN: SQL Server Backup to URL Best Practices and Troubleshooting"

I found your post because I too am struck with the 50 error, even after reading "MSDN: Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases"

I think you should access http://portal.azure.com and then login to your Azure subscription. You change "directories" from Trial to Pay-as-you-go in the top right of the screen...

BTW I think you may need Shared Access Signature in SQL 2016 because when I copy a SQL 2012 script {Backup DB X using CREDENTIAL Y} to 2016 instance, it fails (FORBIDDEN) DESPITE them saying you can use credentials...

Also, Operating System error 50 (The request is not supported), leads me to think server 2012 r2 may be the error... testing currently on my laptop (windows 8) yields same error.

Good luck.

Related Topic