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:

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. Seethe following image:

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

CREATE CREDENTIAL [ Credentials To Connect Azure Storage ]

WITH IDENTITY = ‘sqlbkpstorageaccount’ ,

SECRET = ‘mQm1/TtieAhD/hHvY6V2e**********************SBJVvvLrUVbLwiA==’ ;

In the script,

1. **Specify **thename 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:

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

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:

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:

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:

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:

#azure #backup and restore #maintenance