Search code examples
sql-serverazurepowershellazure-blob-storagedatabase-backups

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

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?

enter image description here

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/

Solution

  • 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:

    enter image description here