How to backup a SQL database to an Azure URL using Ola Hallengren's stored procedures

ola-sql-backup-to-url.md

Description

How to backup a SQL database to an AZURE storage account using the Ola Hallengren scripts.

Requires the stored procedures from: https://ola.hallengren.com/sql-server-backup.html

Create an azure storage account

First, create an azure storage account with block blob support (v2 will suffice) and then create a blob container within that. For this example we'll call it sqlbackup.

Once created, generate an SAS token which allows full read/write access to the container and copy off the url. The url will have two parts and should look like this:

https://<storagename>.blob.core.windows.net/?sv=reallylongtokenstring

Setup credentials in SQL Server

The next step is to store the SAS credentails in SQL Server so that it can be used by the backup command. To do this, open a query window on the master db and run the following sql (with <> substitutions):

IF NOT EXISTS  
(SELECT * FROM sys.credentials   
WHERE name = 'https://<storageaccount>.blob.core.windows.net/<containername>')  
CREATE CREDENTIAL [https://<storageaccount>.blob.core.windows.net/<containername>] 
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
   SECRET = '<token string starting with sv>';  

Create a SQL Agent Backup Job

Create a new SQL Agent job which references the DatabaseBackup stored proc like so:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@BackupType = 'FULL',
@Compress='Y',
@Url='https://<storageaccount>.blob.core.windows.net/<containername>',
@BlockSize=65536,
@MaxTransferSize=4194304

The key to this working is that the Url must match the SQL Server credential name created above.

Also, the BlockSize and MaxTransferSize settings are import for working with large dbs. See here for more guidance: https://blogs.msdn.microsoft.com/sqlcat/2017/03/10/backing-up-a-vldb-to-azure-blob-storage/

With that, the backups should start flowing!

Thanks for the post.
I have a question about the SQL Server backups retention on the Block blob storage:
Is there a way to set the retention using Ola's command?
I not what is the best way to set the retention on the Azure Block Blob Storage? In may case I want to set my backups to 16 days retention than delete them from the Blob Storage.

Thanks
Alan

Hi Alan. Last time I checked the scripts there was not. To solve for this I created a logic app which runs once per week and clears out all my blobs which are older than 30 days.

Hope that helps!

I think it is necessary to specify the name of the credential in the script

@credential nvarchar(max) = NULL,

Hi Abdallah.

Looks like this is defaulted to NULL already in the sproc definition so you shouldn't need to specify that when calling - https://github.com/olahallengren/sql-server-maintenance-solution/blob/588c16545cdb59432489f73695fbb7f6d0161112/DatabaseBackup.sql#L42

With an SQL Server 2012 sp4 (11.0.7507.2) I get this error messages when I try to backup to URL:
Msg 50000, Level 16, State 4, Procedure DatabaseBackup, Line 2276
BLOCKSIZE is not supported when backing up to URL with page blobs. See https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url

Msg 50000, Level 16, State 4, Procedure DatabaseBackup, Line 2276
MAXTRANSFERSIZE is not supported when backing up to URL with page blobs. See https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url

Documentation (https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017) says 'Specifying MAXTRANSFERSIZE is not supported page blobs.' and the same for BLOCKSIZE, but it's exactly the example posted which is performing a backup to Azure Storage...

I've not tried backing up to page blocks before so can't really speak to whether or not the ola scripts support this. I'd recommend trying with block blobs.

For me Azure constructed the SAS URL slightly different which broke the example. To clarify:

<token string starting with sv>

.. should read

<token string starting after "?">

Thanks @scheffler, very useful! Directed me down the right path.

If you are backing up to Azure Storage Account from a SQL Managed Instance and want to use the Managed Instance's managed identity you need to create the credential like so:

CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<containername>] 
WITH IDENTITY = 'MANAGED IDENTITY'  

Permission the managed identity on the storage account with RBAC rules.

You will also need to add this additional predicate to the following where clause in the dbo.DatabaseBackup stored procedure:

OR UPPER(credential_identity) = 'MANAGED IDENTITY'