Backup to URL is a common method used in SQL Server to perform backups to Azure Blob Storage. In this blog post, we will discuss how to generate a script to create credentials and backup commands using access keys.

There are two methods to connect to the storage account when using Backup to URL:

1. Credential by using Access Keys
2. Credential by using SAS token

In this blog post, we will focus on the first method – Backup using Access Keys.

Step-by-Step Guide

To use the script provided below, you will need to copy some values from the Azure Portal. Follow the steps below:

1. Go to Azure Portal Home > Storage accounts > Choose Storage account > Access keys.
2. Copy the Storage Account Name, Container Name, and Storage Key.
3. If you don’t have a container created already, click on the “+” symbol and create a new one.

Script to Backup to URL (with Credentials)

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

SELECT @StorageAccountName = '';--- Find this from Azure Portal
SELECT @ContainerName = '';--- Find this from Azure Portal
SELECT @StorageKey = '';--- Find this from Azure Portal
SELECT @DatabaseName = 'master';
SELECT @CredentialName = 'Cred' + @StorageAccountName;

IF NOT EXISTS (
		SELECT *
		FROM sys.credentials
		WHERE name = '' + @CredentialName + ''
		)
BEGIN
	SELECT @TSQL = 'CREATE CREDENTIAL [' + @CredentialName + '] WITH IDENTITY = ''' + @StorageAccountName + ''' ,SECRET = ''' + @StorageKey + ''';'
	EXEC (@TSQL)
END  

SELECT @Date = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100), '  ', '_'), ' ', '_'), '-', '_'), ':', '_');
SELECT @TSQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO '
SELECT @TSQL += 'URL = N''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '/' + @DatabaseName + '_' + @Date + '.bak'''
SELECT @TSQL += ' WITH CREDENTIAL = ''' + @CredentialName + ''', COMPRESSION, STATS = 1;'

EXEC (@TSQL)

Once you have executed the script, a container will be created and a backup of the master database will be taken. You can verify this in the Azure Portal.

In a future blog post, we will share a script to use Backup to URL using SAS token. If you found this information useful, please leave a comment and let us know.