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
- Credential by using Access Keys.
- 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) : --- =================================== --- 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)