Here are my previous blogs about Backup to URL, I have share scripts to take backup using Access Keys and SAS Token. One of my blog readers wanted to take stripped backup to Azure Blob. In this blog, I am sharing the script to perform a stripped backup using shared access signature (SAS Token)
SQL SERVER – Backup to URL – Script to Generate Credential and Backup using Shared Access Signature (SAS)
SQL SERVER – Msg 3292: A Failure Occurred While Attempting to Execute Backup or Restore With a URL Device Specified
In this script, I am assuming that we already have credential created using an earlier blog. You need to provide is @StorageAccountName, @ContainerName, @DatabaseName and @NumberOfFiles which you need for striping. You can refer my earlier blogs to find those details from the Azure portal.
---- Backup To URL (using SAS Token and striping) : --- =================================== --- DECLARE @Date AS NVARCHAR(25) ,@TSQL AS NVARCHAR(MAX) ,@ContainerName AS NVARCHAR(MAX) ,@StorageAccountName AS VARCHAR(MAX) ,@SASKey AS VARCHAR(MAX) ,@DatabaseName AS SYSNAME ,@NumberOfFiles AS INTEGER ,@temp_Count AS INTEGER = 1; SELECT @Date = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100), ' ', '_'), ' ', '_'), '-', '_'), ':', '_'); SELECT @StorageAccountName = 'sqldbprodbackups'; --- Find this from Azure Portal SELECT @ContainerName = 'dailybackups'; --- Find this from Azure Portal SELECT @DatabaseName = 'master'; SELECT @NumberOfFiles = 5;-- Greater than 1 SELECT @TSQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO ' WHILE @temp_Count <= @NumberOfFiles BEGIN IF (@temp_Count != @NumberOfFiles) BEGIN SELECT @TSQL += 'URL = N''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '/' + @DatabaseName + '_' + @Date + '_File_' + CONVERT(VARCHAR(10), @temp_Count) + '_of_'+ CONVERT(VARCHAR(10), @NumberOfFiles) + '.bak'',' END ELSE BEGIN SELECT @TSQL += 'URL = N''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '/' + @DatabaseName + '_' + @Date + '_File_' + CONVERT(VARCHAR(10), @temp_Count) + '_of_'+ CONVERT(VARCHAR(10), @NumberOfFiles) + '.bak''' END SET @temp_Count = @temp_Count + 1 END SELECT @TSQL += ' WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 1;' --SELECT (@TSQL) EXEC (@TSQL) --- =================================== ---
As soon as the backup is complete, I could see 5 files in the blog storage, that means the script is working as expected.
Hope this script would someone who wants to strip automated backup. Feel free to modify and use it. Let me know if you have other scripts which you use. Share with the world via the comments section.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
When tried above, I am getting below error message…. SQL Server has been installed in Linux OS in Azure cloud.
Msg 3201, Level 16, State 1, Line 10
Cannot open backup device ‘https://databasesqlbackup.blob.core.windows.net/poidbbackups/file.bak’. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 10
BACKUP DATABASE is terminating abnormally.
make sure SAS token is correct.
I’m getting this error Operating system error 50(The request is not supported.)
SAS token is valid. I have a question here…I’m not seeing that the parameter @SASKey isn’t being used anywhere except the declare section. Then how is it using the credential which I have created using a SAS token.
I’ve been wondering about this to.
Would very much like to hear how this works.
Thanks for the script.