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.

29 Comments


  1. Hello there! I simply would like to give you a huge thumbs up for your great information you have right here on this post.

  2. Hello there! I could have sworn I’ve been to this site before but after reading through some of the post I realized it’s new to me. Anyhow, I’m definitely glad I found it and I’ll be bookmarking and checking back frequently!

  3. Normally I don’t learn article on blogs, however I wish to say that this write-up very pressured me to check out and do so!
    Your writing taste has been amazed me. Thank
    you, quite nice article.

  4. Hi, I try to backup my sql databse following your steps, but I cannot see “AzureSQL” in These databases of Maintenance Plan Wizard in SSMS, do I miss somethings?

  5. I do believe all the ideas you have presented for your post. They’re very convincing and can definitely work. Still, the posts are very quick for novices. Could you please lengthen them a little from next time? Thank you for the post.

  6. This is very interesting, You’re a very skilled blogger. I have joined your rss feed and look forward to seeking more of your magnificent post. Also, I’ve shared your site in my social networks!

  7. After examine just a few of the blog posts on your website now, and I actually like your approach of blogging. I bookmarked it to my bookmark web site record and will likely be checking back soon. Pls try my web site as effectively and let me know what you think.

Leave a Reply

Your email address will not be published. Required fields are marked *