Setting up a Microsoft Azure cloud backup for an SQL database

May 7, 2020

This article provides step by step instructions on how to backup an SQL database on the Microsoft Azure cloud using SQL jobs. The SQL server provides built-in functionality for backups to the Azure cloud. This includes restoration of a previous version of the database in two ways, either the latest download, or using a specific URL link to a backup file stored on the cloud. 

The following procedure assumes that a storage with a container has already been created in the Microsoft Azure Portal. The backup can be implemented with the following steps.

Step 1: Create a new SQL job as shown in the following screenshot:

Image 1 SQL Job

Step 2: Create credentials in the Security section for connecting to the Azure cloud as shown below: 

Image 2 Credentials

The “Identity” field should be filled in with the  Microsoft Azure storage name.  The password is the primary key used in creating Manage Access Keys on Azure cloud.  

Step 3: Use the following code in the SQL job step Transact-SQL Script (T-SQL) and select the desired database to be backed up to Azure cloud.

DECLARE @path VARCHAR(500)

DECLARE @name VARCHAR(500)

DECLARE @filename VARCHAR(256)

DECLARE @time DATETIME

DECLARE @year VARCHAR(4)

DECLARE @month VARCHAR(2)

DECLARE @day VARCHAR(2)

DECLARE @hour VARCHAR(2)

DECLARE @minute VARCHAR(2)

DECLARE @second VARCHAR(2)

 

SELECT @time = GETDATE()

SELECT @year   = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))

SELECT @month  = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),’00’)))

SELECT @day    = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),’00’)))

SELECT @hour   = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),’00’)))

SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),’00’)))

SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),’00’)))

set @name = ‘[Database Name…]‘;

       SET @fileName =  @name + ‘_backup_’ + @year + @month + @day + ‘_’ + @hour + @minute + @second + ‘.BAK’  

  set @path = N’https://[Azure Cloud Url path with container name…]/’ + @fileName;

       BACKUP DATABASE [Database Name…]

TO  URL =  @path 

WITH  CREDENTIAL = N’[Database Name…]Credential’  , NOFORMAT, NOINIT,  

NAME = N'[Database Name…]-Full Database Backup’, NOSKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Step 4: Add the desired schedule (daily, weekly or monthly) in the schedules tab and specify exact time for creating a backup on the Azure cloud. Don’t forget to ‘Enable’ the job after completing the time schedule.

We at MetaSys have developed web applications using ASP.NET MVC Framework, SQL Server and web API. For more info on the kind of projects which are handled by us – https://www.metasyssoftware.com/case-study-dotnet

Leave a Comment

Tags :

Category :