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.


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) :
--- =================================== ---
	,@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;
		FROM sys.credentials
		WHERE name = '' + @CredentialName + ''
	SELECT @TSQL = 'CREATE CREDENTIAL [' + @CredentialName + '] WITH IDENTITY = ''' + @StorageAccountName + ''' ,SECRET = ''' + @StorageKey + ''';'
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;'

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)

, , , , ,
Previous Post
SQL SERVER Analysis Services – Unable to Start Service. Error: The Filename, Directory Name, or Volume Label Syntax is Incorrect
Next Post
SQL SERVER – Event ID:7034 – MSSQLSERVER Service Terminated Unexpectedly. It has Done this 6 time(s)

Related Posts

8 Comments. Leave new

Leave a Reply Cancel reply

Exit mobile version