Backing up your SQL Server databases is crucial for data protection and disaster recovery. In addition to traditional backup methods, SQL Server also provides the option to backup your databases directly to Azure Blob Storage. This allows you to take advantage of the scalability and durability of Azure Blob Storage for storing your backups.

In a previous blog post, I shared scripts for taking backups to Azure Blob Storage using Access Keys and SAS Tokens. However, one of my readers requested a script for performing stripped backups using a shared access signature (SAS Token). In this blog post, I will provide the script for performing a stripped backup using a SAS Token.

Before we proceed, please make sure you have already created the necessary credentials using the instructions provided in my earlier blog post. You will need to provide the following parameters in the script:

• @StorageAccountName: The name of your Azure Storage account.
• @ContainerName: The name of the container in your Azure Blob Storage where you want to store the backups.
• @DatabaseName: The name of the database you want to backup.
• @NumberOfFiles: The number of files you want to split the backup into. This is useful for parallelizing the backup process.

Here is the script for performing a stripped backup to Azure Blob Storage using a SAS Token:

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;'

EXEC (@TSQL)

Once the backup is complete, you will be able to see the specified number of files in your Azure Blob Storage container. This indicates that the script has successfully performed the stripped backup.

I hope this script helps those who are looking to automate stripped backups to Azure Blob Storage. Feel free to modify and use it according to your requirements. If you have any other useful scripts, please share them in the comments section below.