Manage SQL databases in an Azure VM using Azure CLI
Article
Azure CLI is used to create and manage Azure resources from the Command Line or through scripts. This article describes how to manage a backed-up SQL database on Azure VM using Azure CLI. You can also perform these actions using the Azure portal.
Protected container named VMAppContainer;Compute;SQLResourceGroup;testSQLVM
Backed-up database/item named sqldatabase;mssqlserver;master
Resources in the westus2 region
Azure CLI eases the process of managing an SQL database running on an Azure VM that's backed-up using Azure Backup. The following sections describe each of the management operations.
az backup job list --resource-group SQLResourceGroup \
--vault-name SQLVault \
--output table
The output appears as:
Output
Name Operation Status Item Name Start Time UTC
------------------------------------ --------------- --------- ---------- -------------------
e0f15dae-7cac-4475-a833-f52c50e5b6c3 ConfigureBackup Completed master [testSQLVM] 2019-12-03T03:09:210831+00:00
ccdb4dce-8b15-47c5-8c46-b0985352238f Backup (Full) Completed master [testSQLVM] 2019-12-01T10:30:58.867489+00:00
4980af91-1090-49a6-ab96-13bc905a5282 Backup (Differential) Completed master [testSQLVM] 2019-12-01T10:36:00.563909+00:00
F7c68818-039f-4a0f-8d73-e0747e68a813 Restore (Log) Completed master [testSQLVM] 2019-12-03T05:44:51.081607+00:00
Change a policy
To change the policy underlying the SQL backup configuration, use the az backup policy set command. The name parameter in this command refers to the backup item whose policy you want to change. Here, replace the policy of the SQL database sqldatabase;mssqlserver;master with a new policy newSQLPolicy. You can create new policies using the az backup policy create command.
Name Operation Status Item Name Backup Management Type Start Time UTC Duration
------------------------------------ --------------- --------- ----------- ------------------------ -------------------------------- --------------
ba350996-99ea-46b1-aae2-e2096c1e28cd ConfigureBackup Completed master AzureWorkload 2022-06-22T08:24:03.958001+00:00 0:01:12.435765
Create a differential backup policy
To create a differential backup policy, use the az backup policy create command with the following parameters:
--backup-management-type: Azure Workload.
--workload-type: SQL DataBase.
--name: Name of the policy.
--policy: JSON file with appropriate details for schedule and retention.
However, if you've added new databases to the SQL instance later, use the az backup protectable-item initialize command. This command discovers the new databases added.
Then use the az backup protectable-item list cmdlet to list all the databases that have been discovered on your SQL instance. This list, however, excludes those databases on which backup has already been configured. Once the database to be backed-up is discovered, refer to Enable backup on SQL database.
Azure CLI
az backup protectable-item list --resource-group SQLResourceGroup \
--vault-name SQLVault \
--workload-type SQLDataBase \
--protectable-item-type SQLDataBase \
--output table
The new database that you want to back up shows in this list, which appears as:
Name ResourceGroup
------------------------------------ ---------------
g0f15dae-7cac-4475-d833-f52c50e5b6c3 SQLResourceGroup
To verify the status of this operation, use the az backup job show command.
Resume protection
When you stop protection for the SQL database with retain data, you can resume protection later. If you don't retain the backed-up data, you won't be able to resume protection.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.