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.
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)
8 Comments. Leave new
thanks fro sharing this valuable post..it’s so helpful..
Very helpful as always. Can you also share a blog on how to Restore from a URL using said access keys or SAS
Thanks for sharing, any clue on how to overwrite existing backup file if it is already present?
You have included screenshots of all ins and outs. Have not read any text because images and SQL is self explanatory.
Thanks a bunch!