Cannot Backup SQL Database to Azure Blob Storage with PowerShell or T-SQL

Question

I believe I am following Microsoft's documentation for backing up SQL databases to Azure Blob Storage; however, I keep hitting the same error no matter what I try.

For example, the following code creates a SQL credential and attempts to backup a database.

Upon running it, the error states that I cannot use WITH CREDENTIAL and SAS, but Microsoft demonstrates using both directly in their documentation (https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017#Examples)!

declare @databaseName varchar(50)
declare @destinationAzureStorageContainerPath varchar(256)
declare @destinationBlobPath varchar(256)
declare @timestampUtc as nvarchar(30)

select @timestampUtc = replace(convert(nvarchar(30), getutcdate(), 126), ':', '_');
set @databaseName = 'DWConfiguration'
set @destinationAzureStorageContainerPath = 'https://mystorageaccount.blob.core.windows.net/mystoragecontainer/'
SET @destinationBlobPath = @destinationAzureStorageContainerPath + @databaseName + '_' + @timestampUtc + '.BAK'

if not exists
(select * from sys.credentials   
where name = 'https://mystorageaccount.blob.core.windows.net/mystoragecontainer/')  
create CREDENTIAL [https://mystorageaccount.blob.core.windows.net/mystoragecontainer/] 
  with IDENTITY = 'SHARED ACCESS SIGNATURE',
  SECRET = 'sv... this is my token ...';

backup DATABASE @databaseName   
to URL = @destinationBlobPath
with CREDENTIAL = 'https://mystorageaccount.blob.core.windows.net/mystoragecontainer/'
,COMPRESSION  
,STATS = 5

The error:

Msg 3225, Level 16, State 1, Line 28 Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature. Msg 3013, Level 16, State 1, Line 28 BACKUP DATABASE is terminating abnormally.

As an alternative approach, I decided to use PowerShell.

Backup-SqlDatabase -ServerInstance "myserver" -Database "DWConfiguration" -BackupFile "https://mystorageaccount.blob.core.windows.net/mystoragecontainer/mydatabase_2019-01-04T20_01_03.127.bak" -SqlCredential "https://mystorageaccount.blob.core.windows.net/mystoragecontainer/"

As you can see, it results in the same annoying error!

Backup-SqlDatabase : System.Data.SqlClient.SqlError: Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.

On the blob itself, I have set "Private (no anonymous access)." I only want authenticated requests to be able to access the blob. Could this be the problem? If so, why doesn't WITH CREDENTIAL resolve this?

How can I simply save a backup of my SQL database to my Azure Storage Account?

Ref. https://blog.sqlauthority.com/2018/07/17/sql-server-backup-to-url-script-to-generate-credential-and-backup-using-shared-access-signature-sas/

Did you read this blog post? blogs.technet.microsoft.com/msftpietervanhove/2016/02/05/… — Mötz
– Mötz, Commented Jan 4, 2019 at 21:41
Yes, it seems to say that, "WITH CREDENTIAL is deprecated" as of SQL 2016. However, when I try creating the backup without using "WITH CREDENTIAL," it fails to authenticate. It seems like it is required but it's not letting me use it! — user1477388
– user1477388, Commented Jan 4, 2019 at 21:42
This example appears to omit the WITH CREDENTIAL altogether. learn.microsoft.com/en-us/sql/t-sql/statements/…. What error message are you getting when you omit it? — Nick.Mc
– Nick.Mc, Commented Jan 5, 2019 at 6:32
The error I get when I don't include the WITH CREDENTIAL is: Cannot open backup device 'mystorageaccount.blob.core.windows.net/mystoragecontainer/…'. Operating system error 50(The request is not supported.). Msg 3013, Level 16, State 1, Line 28 BACKUP DATABASE is terminating abnormally." — user1477388
– user1477388, Commented Jan 7, 2019 at 13:53

Ivan Glasenberg · Accepted Answer · 2019-01-08 09:33:50Z

3

I think you made a mistake in your sql scripts.

You created the credentials "Using Shared Access Signature", but when backup you used "To URL using storage account identity and access key" which is not matched the sas you created before.

I test it at my side, and works fine(create credentials "Using Shared Access Signature", and backup using "To URL using Shared Access Signature").

IF NOT EXISTS (SELECT * FROM sys.credentials   
               WHERE name = 'https://xxx.blob.core.windows.net/container')  
CREATE CREDENTIAL [https://xxx.blob.core.windows.net/container] 
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = 'sv=xxx';


BACKUP DATABASE MyTest   
TO URL = 'https://xxx.blob.core.windows.net/container/123.bak'  
GO

Test result as below:

answered Jan 8, 2019 at 9:33

Ivan Glasenberg

30.3k2 gold badges56 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1477388 Over a year ago

Is your storage account deployment model set to "classic" or "resource manager?" I think my issue could be caused by the fact that my storage account is set to "resource manager" as described here blogs.msdn.microsoft.com/sqlserverstorageengine/2017/10/17/… "BACKUP TO URL can backup database to Classic Standard storage account."

Ivan Glasenberg Over a year ago

I have checked, my storage account deployment model is "resource manager".

user1477388 Over a year ago

I compared your SQL code to mine and found that mine had a trailing forward slash on the 'https://xxx.blob.core.windows.net/container' SQL credential. Once I removed the slash, it worked! How strange is that! Thanks for your help.

alper Over a year ago

I am getting following error message:

BACKUP DATABASE failed. SQL Database Managed Instance supports only COPY_ONLY full database backups which are initiated by user.

what should I do?

Collectives™ on Stack Overflow

Cannot Backup SQL Database to Azure Blob Storage with PowerShell or T-SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related