Nisarg Upadhyay
Backup database task

Backup SQL databases to Azure using the database maintenance plan

August 6, 2020 by

In this article, we are going to learn how we can back up the SQL database to Azure using a database maintenance plan. To demonstrate the process, I have restored the AdventureWorks2017 database on my workstation. I have created an Azure container named sqlbackups in my storage account.

To view the storage account, log in to the Azure portal -> Click on Storage accounts -> on Storage Accounts screen, you can view the list of the storage accounts that have been created. See the following image:

View Azure Storage Account

Microsoft does not support the backup to the URL with the SAS token. If you try to create it using SAS token, you will receive the following error:

Msg 3225, Level 16, State 1, Line 5
Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.
Msg 3013, Level 16, State 1, Line 5
BACKUP DATABASE is terminating abnormally.

To fix the issue, we must create a SQL Server credentials using Access keys of the Azure storage account. To copy the access keys, log in to the Azure Portal -> Navigate to the Storage Account -> Click on Access Keys -> Copy the storage key specified in the key 1 textbox. See the following image:

Access keys of the Azure storage account

Now, let us create a SQL Server credentials using the access keys. To do that, execute the below script:

In the script,

  1. Specify the name of the storage account in the IDENTITY clause
  2. Provide an access key token in the SECRET clause

Once credentials are created, we are going to use them to connect to the Azure storage account.

Create a database maintenance plan

To create a maintenance plan, Open SQL Server Management Studio -> Connect to the database engine -> Expand Management -> Right-click on the Maintenance plan. See the following image:

Create maintenance plan for SQL Database

Drag the Back Up Database Task from the toolbar and drop it on the maintenance plan designer. See the following image:

Backup database task

Double-click on Back Up Database Task. A dialog box opens to configure the settings of the maintenance plan. As mentioned, we want to generate the backup of the AdventureWorks2017 SQL Database so on the dialog box, click on the database (s) and select AdventureWorks2017 database from the list and click on OK. See the following image:

Specify AdventureWorks2017 SQL Database

Select the database from the component section. To back up the SQL Database to Azure, instead of Disk location, we must provide the URL of the Azure storage container. To do that, select URL from the Back up to the drop-down box. See the following image:

Backup to URL

To configure the backup destination, click on the Destination tab of the dialog box. From the SQL credentials drop-down box, choose the [Credentials To Connect Azure Storage]. When you select it, the name of the Azure storage container, URL prefix, and backup extension will be populated automatically. See the following image:

Define Credentials to connect to Azure Storage account

From the Options tab, you can specify the following details:

  1. Set the backup compression
  2. Generate the copy-only backup
  3. Verify backup integrity
  4. Encrypt the backup

We do not want to change any other configuration, so click OK to save the maintenance plan and close the window.

Once the backup job is created, let us configure the notification operator. We want to create a notification for success and failure, so we must add two notification operator tasks from the toolbox. To do that, drag the notify operator task from the toolbox and drop on the maintenance plan designer. See the following image:

Notify operator task

Now, drag and drop the arrow from the Backup database task to Notify the operator task. Right-click on the arrow and choose “Success” from the context menu. See the following image.

Configure the notification path

Similarly, drag and drop another arrow on Notify Operator task 1, right-click on the arrow and choose failure. Now, let us configure the Notify Operator task.

To configure the notify operator task, double click on it. On notify operator task dialog box, select the desired operator from Operators to notify the list box. In the Subject text box, provide the desired subject line. In the notification message body, you can specify the email details. See the following image:

Configure Notify operator task

Click OK to Save the notify operator task and close the dialog box. The entire maintenance plan looks like the following image:

Entire maintenance plan

Click on Save to save the maintenance plan. Now to test the maintenance plan, expand management -> expand maintenance plan -> Right-click on the maintenance plan and click on Execute. See the following image:

Execute maintenance plan to generate backup of SQL Database

Once the maintenance plan completes successfully, a notification email will be sent from SQL Server that the backup is completed successfully. You can see that the backup of the AdvantureWorks2017 database has been generated and uploaded to the blob container. See the following image:

Backup has been generated and uploaded to Blob container

To automate the maintenance plan, expand SQL Server Agent -> Expand Jobs -> Right-click on Backup to Azure.Subplan_1 and click on Properties. See the following image:

Open properties of maintenance plan

On Job properties dialog box (Screen 1), click on schedule. On the new Job schedule dialog box (Screen 2), provide the desired name of the schedule and configure the job execution schedule accordingly. See the following screenshot:

Configure the schedule

Summary

In this article, we learned how we can fix the ‘WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature’ error. We also learned to back up the SQL database to Azure Blob storage using the SQL Server maintenance plan.

Nisarg Upadhyay
Azure, Backup and restore, Maintenance

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

168 Views