As mentioned in a previous blog post, backup to URL is a common method used in SQL Server to perform backups to Azure Blob Storage. In this blog post, I will share a script that generates the create credential and backup command using Shared Access Signature (SAS) token.

Backup to URL has two methods to connect to the storage account: Credential by using Access Keys and Credential by using SAS token. In a previous blog post, I shared a script for the first method. In this blog post, I will show you the second method – Backup using Shared Access Signature.

Script Parameters

In the script, we need to provide the following parameters:

• @StorageAccountName: This is the name of the storage account in Azure portal.
• @ContainerName: This is the name of the container in the storage account.
• @SASKey: This is the Shared Access Signature (SAS) key generated in the Azure portal.

Backup To URL (using SAS Token)

DECLARE @Date AS NVARCHAR(25)
	,@TSQL AS NVARCHAR(MAX)
	,@ContainerName AS NVARCHAR(MAX)
	,@StorageAccountName AS VARCHAR(MAX)
	,@SASKey AS VARCHAR(MAX)
	,@DatabaseName AS SYSNAME;

SELECT @Date = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100), '  ', '_'), ' ', '_'), '-', '_'), ':', '_');
SELECT @StorageAccountName = ''; --- Find this from Azure Portal
SELECT @ContainerName = ''; --- Find this from Azure Portal
SELECT @SASKey = ''; --- Find this from Azure Portal
SELECT @DatabaseName = 'master';

IF NOT EXISTS (
		SELECT *
		FROM sys.credentials
		WHERE name = '''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + ''''
		)
BEGIN
	SELECT @TSQL = 'CREATE CREDENTIAL [https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '] WITH IDENTITY = ''SHARED ACCESS SIGNATURE'', SECRET = ''' + REPLACE(@SASKey, '?sv=', 'sv=') + ''';'
	--SELECT @TSQL
	EXEC (@TSQL)
END

SELECT @TSQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO '
SELECT @TSQL += 'URL = N''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '/' + @DatabaseName + '_' + @Date + '.bak'''
SELECT @TSQL += ' WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 1;'
--SELECT @TSQL
EXEC (@TSQL)

Once the script is executed, you will be able to see the credential in SQL Server Management Studio (SSMS) and the backup in Azure.

I hope this script helps you in creating backups to Azure Blob Storage using Shared Access Signature (SAS) token in SQL Server.