SQL database scheduled backup to Azure Blob

In this article, we’ll walk through how to automate backups of multiple SQL Server databases directly to Azure Blob Storage using SQL Server Agent. This is ideal for hybrid or cloud-first architectures where backups must be offloaded to Azure for durability, compliance, or disaster recovery purposes.

Before proceeding, ensure you have:

• ✅ SQL Server 2012 SP1 CU2 or later (for TO URL support)

Step 1: Create an Azure Blob Container & SAS Token

1. Go to Azure Portal

2. Navigate to your Storage Account → Containers

3. Create a new container (e.g., SQL-backup)

4. (optional) If hierarchical namespace was enabled during creation, you can further create folders to separate the folders such as year-wise, month-wise, etc. after this u can edit the filename in the T-SQL command.

5. Generate a SAS token with:

   • Permissions: Write, List, Create, Add

   • Expiry: long enough (e.g., 1 year)

6. Copy the container URL and SAS token.

Step 2: Create a Credential in SQL Server

In SSMS, run the following:

1. Change the storage account and the secret according to the SAS token created above

CREATE CREDENTIAL [https://yourstorageaccount.blob.core.windows.net/trl-backup]
WITH IDENTITY = 'Shared Access Signature',
SECRET = 'your-sas-token-starting-with-?';

Note: The SECRET must include the leading ?.

Step 3: Write the Backup Script with Compression & Timestamp

DECLARE @DBName NVARCHAR(255);
DECLARE @FileName NVARCHAR(1000);
DECLARE @Command NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
-- Change this WHERE clause to exclude system DBs
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct dynamic file name using DB name and timestamp
    SET @FileName = 'https://yourstorageaccount.blob.core.windows.net/SQL-backup/' +
                    @DBName + '_' +
                    CONVERT(NVARCHAR, GETDATE(), 112) + '_' +
                    REPLACE(CONVERT(NVARCHAR, GETDATE(), 114), ':', '') + '.bak';

    -- Build the backup command
    SET @Command = 'BACKUP DATABASE [' + @DBName + '] TO URL = N''' + @FileName + ''' WITH COMPRESSION;';

    -- Print for debugging
    PRINT @Command;

    -- Execute the backup command
    EXEC sp_executesql @Command;

    FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

This T-SQL script automates the backup of all user databases in a SQL Server instance to an Azure Blob Storage container using dynamic file names. It begins by declaring variables and opening a cursor that selects all databases except the system ones (master, tempdb, model, and msdb). For each database in the list, it dynamically constructs a backup file name using the database name and the current date and time, ensuring uniqueness. It then builds and executes a BACKUP DATABASE command using the TO URL option (which sends the .bak file directly to Azure Blob Storage) and applies WITH COMPRESSION to reduce the backup size. The process loops through all eligible databases, creating a compressed backup for each. Finally, the script closes and de-allocates the cursor. This script is especially useful when scheduled as a SQL Server Agent Job for automated, recurring offsite backups to the cloud.

Step 4: Create a SQL Server Agent Job

1. Open SQL Server Management Studio (SSMS)

2. Navigate to SQL Server Agent → New → Job...

3. Set a name (e.g., Azure Blob DB Backup)

4. Go to the Steps tab → Add a new step:

   • Set a name for the step  (e.g. DB backup step)

   • Type: Transact-SQL

   • Database: master (or any) - this doesn't matter as the code passes all the databases anyways

   • Paste the script above

5. Go to the Schedules tab:

   • Create a recurring schedule (e.g., daily at 12 AM)

   • Choose New... -> choose recurring for type -> occurs daily/weekly/monthly as per requirement -> choose frequency to be once per day or at multiple instances throughout the day -> choose no end date to create daily schedule. ( These all options can be changed later on).

   • Change the date and time from PC settings to match the time in the PC and time shown in the app.

   • 

6. Click OK to save.

To test if the command is successful, use the step below:

Output Example

Once the job runs, you’ll see .bak files in your Azure Blob container like:

(db_name_YYYYMMDD_HHMMSS.bak)

db1_20250714_142101.bak
db2_20250714_142103.bak
db3_20250714_142105.bak