Automating Sql Server Backups to Azure Blob Storage

Overview

This guide outlines how to automate SQL Server backups to Azure Blob Storage. This guide will use a SAS token to authenticate to Azure Blob Storage. This is a good way to automate backups where local storage is limited or not available.

Prerequisites

SQL Server 2014 or newer and SMSS
Azure Storage Account

Monitoring

Monitoring is important to quickly identify issues with backups. Monitoring does not replace testing backups, but it can help identify issues before they become a problem. If the server is in Zabbix, you can monitor the SQL backup with this solution .

Setup SQL Backup

Step 1: Create SAS Token

Go to the Azure Portal and navigate to the Storage Account you want to use. In the Containers view, select (or create) the container you want to use and click Generate SAS. Set the Start and End dates to the desired time frame. Set all permissions except Immutable storage and click Generate SAS and connection string.

Create SAS Token

Copy the Blob SAS token. Also take note of the Storage account name and Container name.

Step 2: Create SQL Credential

For each SQL instance, execute the following SQL query on the server, replacing the SAS token, and with the values from the previous step:

1
2
3
4
5
USE [master]
GO
CREATE CREDENTIAL [https://<storage account name>.blob.core.windows.net/<container name>]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS token>'

Note: There should be brackets around URL and single quotes around the values IDENTITY and SECRET. The value of IDENTITY must be 'SHARED ACCESS SIGNATURE'.

Step 3: Create SQL Backup Script

The script must be run on master for each instance. The script creates a folder with today’s date (YYYY-MM-DD) and splits the BAK files into 3. This is because for large database files fail if there is only 1 BAK file. If you run SQL Express, you must remove the COMPRESSION flag at the end of the backup command (line: 72).

The script is stored under Stored Procedures in master.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
-- Copyright   Microsoft Corporation.  All Rights Reserved.
-- This code released under the terms of the
-- Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backup url, ex: storageaccount.file.core.windows.net
-- Parameter3: backup storage blob, ex: backup-blob01
-- =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases]
            @databaseName sysname = null,
            @backupStorageAccount nvarchar(200),
            @backupStorageBlob nvarchar(200)
AS
       SET NOCOUNT ON;
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name= ISNULL(@DatabaseName ,name)
            ORDER BY Name

            -- Filter out databases which do not need to backed up
            DELETE @DBs where DBNAME IN ('tempdb')

            -- Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile1 varchar(300)
            DECLARE @BackupFile2 varchar(300)
            DECLARE @BackupFile3 varchar(300)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000)
            DECLARE @dateTime NVARCHAR(20)
            DECLARE @dateOnly NVARCHAR(20)
            DECLARE @Loop int

            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
      WHILE @Loop IS NOT NULL
      BEGIN
-- Database Names have to be in [dbname] format since some have - or _ in their name
        SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format
		SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
		SET @dateOnly = CONVERT(VARCHAR, GETDATE(),23)

-- Create backup filename in path\filename.extension format for full,diff and log backups
        SET @BackupFile1 = 'https://'+@backupStorageAccount+'/'+@backupStorageBlob+'/'+@dateOnly+'/'+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '-1.BAK'
        SET @BackupFile2 = 'https://'+@backupStorageAccount+'/'+@backupStorageBlob+'/'+@dateOnly+'/'+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '-2.BAK'
        SET @BackupFile3 = 'https://'+@backupStorageAccount+'/'+@backupStorageBlob+'/'+@dateOnly+'/'+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '-3.BAK'

-- Provide the backup a name for storing in the media
        SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed
        SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO URL = N'''+@BackupFile1+''', URL = N'''+@BackupFile2+ ''', URL = N'''+@BackupFile3+ ''' WITH COPY_ONLY, NOFORMAT, NOINIT, NAME= ''' +@BackupName+''', NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION'

-- Execute the generated SQL command
        EXEC(@sqlCommand)

-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
END

Step 4: Schedule SQL Backup

Open SMSS and connect to the SQL instance you want to backup.

Create-SQL-Agent-Job

In SMSS, navigate to the SQL instance you want to backup. Right click on SQL Server Agent and select New -> Job. Give the job a name and click Steps. Click New and give the step a name. Select Transact-SQL script (T-SQL) as the type and paste the following into the command box, replacing and with the values from the SAS token:

1
EXEC [dbo].[sp_BackupDatabases] @backupStorageAccount = '<storage account name>.blob.core.windows.net', @backupStorageBlob = '<container name>'

Adjust the agent job properties and schedule as desired. Click OK to save the job.

Alternatively, you can run the script with Task Scheduler. Create a new backup_sql.cmd file with the following contents, replacing and with the values from the SAS token:

1
sqlcmd -S localhost -E -Q "EXEC [dbo].[sp_BackupDatabases] @backupStorageAccount = '<storage account name>.blob.core.windows.net', @backupStorageBlob = '<container name>'"

Then use Task Scheduler to run the script as desired. This requires the user running the scheduled task to have access to SQL Server.

Step 5: Adjust Retention and Lifecycle Management

Back to the Storage Account in Azure Portal, go to Lifecycle Management. If this Storage Account is only used for this SQL backup, you may use Apply rule to all blobs in your storage account, otherwise you may want to use Limit blobs with filters.

Create-Retention-Policy-1

Set Blob type to Block blob and Blob subtype to Base blobs. Click Next.

Create-Retention-Policy-2

Add conditions as desired. Data stored in Archive storage is cheaper to store, but more expensive to access. You should therefore only move to Archive storage if you are unlikely to need to restore the data. Click Add to save the policy.

Conclusion

This is one way to automate SQL Server backups to Azure Blob Storage. Remember to regularly test your backups and ensure they are working as expected.

Overview#

Prerequisites#

Monitoring#

Setup SQL Backup#

Step 1: Create SAS Token#

Step 2: Create SQL Credential#

Step 3: Create SQL Backup Script#

Step 4: Schedule SQL Backup#

Step 5: Adjust Retention and Lifecycle Management#

Conclusion#