Backup to URL not working when using Ola Hallengrens Database Backup

Posted on

Question :

I am using Ola Hallengrens DB backup and am trying to get it working with the URL parameter.

I first set up a Shared Access Credential in SQL with the following sql

USE MASTER  
CREATE CREDENTIAL [https://exampleblob.blob.core.windows.net/backups] 
WITH IDENTITY='SHARED ACCESS SIGNATURE'
,SECRET = 'sv=2015-04-05&ss=bf&srt=sco&sp=rwd&se=2030-11-14T20:07:06Z&st=2016-11-13T12:07:06Z&spr=https&sig=KEY'
GO  

Next I tried using the following in my log backup maintenance plan:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBATools -Q 
"EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'USER_DATABASES', 
@BackupType = 'LOG', 
@Verify = 'Y', 
@Compress = 'Y', 
@CheckSum = 'Y', 
@LogToTable = 'Y', 
@ChangeBackupType='Y', 
@url='https://exampleblob.blob.core.windows.net/backups', 
@Credential='https://exampleblob.blob.core.windows.net/backups'" -b

However when I run the job I get the following error

Use of WITH CREDENTIAL syntax is not valid for credentials containing
a Shared Access Signature

Answer :

I just ran into this issue as well creating a Maintenance Plan in SQL Server. The default backup maintenance plan creates a script that includes WITH CREDENTIAL, but I was able to get it working when I removed that default Task. So I ended up with this code in an Execute T-SQL Statement Task:

declare @backuptime as varchar(250);
set @backuptime = 'https://youraccountname.blob.core.windows.net/backups/mytestdb_' + CONVERT(VARCHAR(19), GETDATE(), 120) + '.bak';

BACKUP DATABASE yourdb   
    TO URL = @backuptime

If you open Properties of ‘Back Up Database Task’ and set ‘CredentialName’ to empty, then saving the plan and running the job actually succeeds.

Tried with SSMS 13.0.16100.1

Leave a Reply

Your email address will not be published. Required fields are marked *