Schedule MS SQL database backup to Azure storage

As I promised in one of previous posts, I’ll write about scheduling MS SQL database to Azure Storage Account.

We’ll start with creating new Storage Account. Go to Azure Portal and click ‘New’. Select ‘Storage’ and ‘Storage account’.

1

Enter parameters for your Storage account. Enter name, deployment model must be ‘Classic'(as ARM will not be visible from SSMS), select ‘General purpose’, performance can be set either to Standard or Premium. For replication you can set whatever you want (unless you choose Premium storage, then you can’t choose Geo-redundant), encryption also can be set to whatever you want. Select your subscription, resource group and location. Finally, click ‘Create’.

2

After deployment is finished, go to your Storage account, select Blobs and click add new container.

3

In new tile, enter name for your container and access type. I recommend you set it to ‘Private’ so no one without access key or certificate can’t Access your data.

4

After Storage account is set, open SSMS and connect to SQL Server. Go to management, maintenance and select maintenance plan wizard.

5

Click next.

6

In this window, select change to create backup schedule.

7

In job Schedule, set to recurring (and enabled), select frequency of your backup and time you want to be executed at.

8

Click next.

9

Select type of backup you want. I will check full. You can choose additional maintenance tasks if you want.

10

In task order you can choose order that your tasks will execute in. if you only selected backup, just click next.

11

Now select databases that you want to backup.

12

You can choose different options here, depending on what you want to do. I will set it to single database named AzureSQL.

13

Set ‘Back up to:’ to URL.

14

Go to destination. If you already have SQL credentials for Azure backup, it will show in dropdown menu. If you don’t have one, click ‘Create’.

15

You have few different options here. You can use certificate or publish setting file. If you want to use certificate, use makecert.exe and upload it to your Azure subscription. For publish setting file, you can go to Azure portal and download it or use PowerShell command Get-AzurePublishSettingsFile to do that.

16

After you download publish settings file, import it.

17

Now your certificate and subscription will be automatically selected and you need to select Storage account.

18

Credentials and URL will be generated and you need to enter name of container you created for your backup.

19

Click next, finish and close. Your backup is scheduled.

20

21

22

Now let’s test our backup plan. Left click on plan you plan and select execute.

23

After you get message that it finished, go to Azure portal and your Storage account. After you click on container, you should see your backup.

24

Article by Mustafa Toroman

I’m System Engineer at Authority Partners. I love to test and explore new technologies. Have over 20 active MS certificates such as MCSA for Windows Server and SQL Server, MCSE Private Cloud, Data Platform, Business Intelliegence and Server Infrastracture. Lately most interested in BI and Azure solutions. MCT since 2012. From 2016 MVP for Microsoft Azure.