SQL SERVER – Backup to URL – Script to Perform Stripped Backup using Shared Access Signature (SAS)

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

WORKAROUND/SOLUTION

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.

SQL SERVER - Backup to URL - Script to Perform Stripped Backup using Shared Access Signature (SAS) sas-stripped-backup-01

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)

, , ,
Previous Post
SQL SERVER – Backup to URL – Script to Generate Credential and Backup using Shared Access Signature (SAS)
Next Post
SQL SERVER – Backup Error: 3636 – An Error Occurred While Processing BackupMetadata

Related Posts

4 Comments. Leave new

  • Satish Prithviraj Pandharpurkar
    July 25, 2018 7:28 pm

    Hi,

    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.

    Reply
  • Amrita Sen Chakraborty
    July 6, 2020 3:15 am

    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.

    Reply
  • Jan-Willem Aikens
    September 14, 2020 11:52 am

    I’ve been wondering about this to.
    Would very much like to hear how this works.
    Thanks for the script.

    Reply

Leave a Reply

Menu