SQL SERVER – Backup to URL – Script to Generate the Create Credential and Backup Command using Access Keys

Backup to URL is one of the common methods used in SQL Server perform backup to Azure Blob Storage. In this blog, I am going to share a script to generate the create credential and backup command using access keys.

If you don’t know already, Backup to URL also has two methods to connect to the storage account

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

In this blog, I would show the first method – Backup using Access Keys.

WORKAROUND/SOLUTION

To use below script, you need to copy some values from Azure Portal. Go to Azure Portal Home > Storage accounts > Choose Storage account > Access keys as shown below:

To get a container name, you can refer below screenshot. You need to click on “Browse Blobs”. If you don’t have container created already then click on “+” symbol and create a new one. In my Azure, I have already created one called “dailybackups” as shown below.

---- 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 + ''';'
	-- PRINT @TSQL
	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;'
-- PRINT @TSQL
EXEC (@TSQL)

Above script would create a container and take backup of the master database. Once I ran the script, I could see Credential create and backup was also taken as shown below.

Here is the backup

Soon, I would also share a script to use the backup to URL using SAS token via another blog. If you find this useful, please comment and let me know.

Reference: Pinal Dave (http://blog.SQLAuthority.com)