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. See the following image:
Now, let us create a SQL Server credentials using the access keys. To do that, execute the below script:
|
1 2 3 |
CREATE CREDENTIAL [Credentials To Connect Azure Storage] WITH IDENTITY = 'sqlbkpstorageaccount' , SECRET = 'mQm1/TtieAhD/hHvY6V2e*******************************SBJVvvLrUVbLw*********iA=='; |
In the script,
- Specify the name of the storage account in the IDENTITY clause
- 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:
- Set the backup compression
- Generate the copy-only backup
- Verify backup integrity
- 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:
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.
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:
Click OK to Save the notify operator task and close the dialog box. The entire maintenance plan looks like the following image:
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:
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:
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:
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:
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.
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022