Restore SQL databases in an Azure VM using Azure CLI
\Azure CLI is used to create and manage Azure resources from the Command Line or through scripts. This article describes how to restore a backed-up SQL database on an Azure VM using Azure CLI. You can also perform these actions using the Azure portal.
Use Azure Cloud Shell to run CLI commands.
In this article, you'll learn how to:
- View restore points for a backed-up database
- Restore a database
This article assumes you've an SQL database running on Azure VM that's backed-up using Azure Backup. If you've used Back up an SQL database in Azure using CLI to back up your SQL database, then you're using the following resources:
- A resource group named
SQLResourceGroup. - A vault named
SQLVault. - Protected container named
VMAppContainer;Compute;SQLResourceGroup;testSQLVM. - Backed-up database/item named
sqldatabase;mssqlserver;master. - Resources in the
westusregion.
Note
See the SQL backup support matrix to know more about the supported configurations and scenarios.
View restore points for a backed-up database
To view the list of all recovery points for a database, use the az backup recoverypoint list command as:
az backup recoverypoint list --resource-group SQLResourceGroup \
--vault-name SQLVault \
--container-name VMAppContainer;Compute;SQLResourceGroup;testSQLVM \
--item-name sqldatabase;mssqlserver;master \
--output table
The list of recovery points appears as:
Name Time BackupManagementType Item Name RecoveryPointType
------------------- --------------------------------- --------------------- ---------------------- ------------------
7660777527047692711 2019-12-10T04:00:32.346000+00:00 AzureWorkload sqldatabase;mssqlserver;master Full
7896624824685666836 2019-12-15T10:33:32.346000+00:00 AzureWorkload sqldatabase;mssqlserver;master Differential
DefaultRangeRecoveryPoint AzureWorkload sqldatabase;mssqlserver;master Log
The list above contains three recovery points: each for full, differential, and log backup.
Note
You can also view the start and end points of every unbroken log backup chain, using the az backup recoverypoint show-log-chain command.
Prerequisites to restore a database
Ensure that the following prerequisites are met before restoring a database:
- You can restore the database only to an SQL instance in the same region.
- The target instance must be registered with the same vault as the source.
Restore a database
Azure Backup can restore SQL databases that are running on Azure VMs as:
- Restore to a specific date or time (to the second) by using log backups. Azure Backup automatically determines the appropriate full, differential backups and the chain of log backups that are required to restore based on the selected time.
- Restore to a specific full or differential backup to restore to a specific recovery point.
To restore a database, use the az restore restore-azurewl command, which requires a recovery config object as one of the inputs. You can generate this object using the az backup recoveryconfig show command. The recovery config object contains all details to perform a restore. One of them is the restore mode – OriginalWorkloadRestore or AlternateWorkloadRestore.
Note
OriginalWorkloadRestore: Restores data to the same SQL instance as the original source. This option overwrites the original database. AlternateWorkloadRestore: Restores database to an alternate location and keep the original source database.
Restore to alternate location
To restore a database to an alternate location, use AlternateWorkloadRestore as the restore mode. You must then choose the restore point, which could be a previous point-in-time or any previous restore points.
Let's proceed to restore to a previous restore point. View the list of restore points for the database and choose the point you want to restore. Here, let's use the restore point with the name 7660777527047692711.
With the above restore point name and the restore mode, create the recovery config object using the az backup recoveryconfig show command. Check the remaining parameters in this command:
- --target-item-name: The name to be used by the restored database. In this scenario, we used the name restored_database.
- --target-server-name: The name of an SQL server that's successfully registered to a Recovery Services vault and stays the same region as per the database to be restored. Here, you're restoring the database to the same SQL server that you've protected, named testSQLVM.
- --target-server-type: For the restore of SQL databases, you must use SQLInstance.
az backup recoveryconfig show --resource-group SQLResourceGroup \
--vault-name SQLVault \
--container-name VMAppContainer;Compute;SQLResourceGroup;testSQLVM \
--item-name SQLDataBase;mssqlserver;master \
--restore-mode AlternateWorkloadRestore \
--rp-name 7660777527047692711 \
--target-item-name restored_database \
--target-server-name testSQLVM \
--target-server-type SQLInstance \
--workload-type SQLDataBase \
--output json
The response to the above query is a recovery config object that appears as:
{
"container_id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SQLResourceGroup/providers/Microsoft.RecoveryServices/vaults/SQLVault/backupFabrics/Azure/protectionContainers/vmappcontainer;compute;SQLResourceGroup;testSQLVM",
"container_uri": "VMAppContainer;compute;SQLResourceGroup;testSQLVM",
"database_name": "MSSQLSERVER/restored_database",
"filepath": null,
"item_type": "SQL",
"item_uri": "SQLDataBase;mssqlserver;master",
"log_point_in_time": null,
"recovery_mode": null,
"recovery_point_id": "7660777527047692711",
"restore_mode": "AlternateLocation",
"source_resource_id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SQLResourceGroup/providers/Microsoft.Compute/virtualMachines/testSQLVM",
"workload_type": "SQLDataBase",
"alternate_directory_paths": []
}
Now, to restore the database, run the az restore restore-azurewl command. To use this command, enter the above JSON output that's saved to a file named recoveryconfig.json.
az backup restore restore-azurewl --resource-group SQLResourceGroup \
--vault-name SQLVault \
--recovery-config recoveryconfig.json \
--output table
The output appears as:
Name Operation Status Item Name Backup Management Type Start Time UTC Duration
------------------------------------ ----------- ---------- --------------------------------- ------------------------ -------------------------------- --------------
be7ea4a4-0752-4763-8570-a306b0a0106f Restore InProgress master [testSQLVM] AzureWorkload 2022-06-21T03:51:06.898981+00:00 0:00:05.652967
The response provides you with the job name. You can use this job name to track the job status using az backup job show command.
Restore and overwrite
To restore to the original location, use OriginalWorkloadRestore as the restore mode. You must then choose the restore point, which could be a previous point-in-time or any of the previous restore points.
As an example, let's choose the previous point-in-time "28-11-2019-09:53:00" to restore to. You can provide this restore point in the following formats: dd-mm-yyyy, dd-mm-yyyy-hh:mm:ss. To choose a valid point-in-time to restore, use the az backup recoverypoint show-log-chain command, which lists the intervals of unbroken log chain backups.
az backup recoveryconfig show --resource-group SQLResourceGroup \
--vault-name SQLVault \
--container-name VMAppContainer;Compute;SQLResourceGroup;testSQLVM \
--item-name sqldatabase;mssqlserver;master \
--restore-mode OriginalWorkloadRestore \
--log-point-in-time 20-06-2022-09:02:41 \
--output json
The response to the above query is a recovery config object that appears as:
{
"alternate_directory_paths": null,
"container_id": null,
"container_uri": "VMAppContainer;compute;petronasinternaltest;sqlserver-11",
"database_name": null,
"filepath": null,
"item_type": "SQL",
"item_uri": "SQLDataBase;mssqlserver;msdb",
"log_point_in_time": "20-06-2022-09:02:41",
"recovery_mode": null,
"recovery_point_id": "DefaultRangeRecoveryPoint",
"restore_mode": "OriginalLocation",
"source_resource_id": "/subscriptions/62b829ee-7936-40c9-a1c9-47a93f9f3965/resourceGroups/petronasinternaltest/providers/Microsoft.Compute/virtualMachines/sqlserver-11",
"workload_type": "SQLDataBase"
}
Now, to restore the database, run the az restore restore-azurewl command. To use this command, enter the above JSON output that's saved to a file named recoveryconfig.json.
az backup restore restore-azurewl --resource-group sqlResourceGroup \
--vault-name sqlVault \
--recovery-config recoveryconfig.json \
--output table
The output appears as:
Name Operation Status Item Name Backup Management Type Start Time UTC Duration
------------------------------------ ----------- ---------- ------------------------------- ------------------------ -------------------------------- --------------
1730ec49-166a-4bfd-99d5-93027c2d8480 Restore InProgress master [testSQLVM] AzureWorkload 2022-06-21T04:04:11.161411+00:00 0:00:03.118076
The response provides you with the job name. You can use this job name to track the job status using the az backup job show command.
Restore to a secondary region
To restore a database to the secondary region, specify a target vault and server located in the secondary region, in the restore configuration.
az backup recoveryconfig show --resource-group SQLResourceGroup \
--vault-name SQLVault \
--container-name VMAppContainer;compute;SQLResourceGroup;testSQLVM \
--item-name sqldatabase;mssqlserver;master \
--restore-mode AlternateWorkloadRestore \
--from-full-rp-name 293170069256531 \
--rp-name 293170069256531 \
--target-server-name targetSQLServer \
--target-container-name VMAppContainer;compute;SQLResourceGroup;targetSQLServer \
--target-item-name testdb_restore_1 \
--target-server-type SQLInstance \
--workload-type SQLDataBase \
--target-resource-group SQLResourceGroup \
--target-vault-name targetVault \
--backup-management-type AzureWorkload
The response is a recovery configuration object that appears as:
{
"container_id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SQLResourceGroup/providers/Microsoft.RecoveryServices/vaults/targetVault/backupFabrics/Azure/protectionContainers/vmappcontainer;compute;SQLResourceGroup;targetSQLServer",
"container_uri": "VMAppContainer;compute;SQLResourceGroup;testSQLVM",
"database_name": "MSSQLSERVER/sqldatabase;mssqlserver;testdb_restore_1",
"filepath": null,
"item_type": "SQL",
"item_uri": "SQLDataBase;mssqlserver;master",
"log_point_in_time": null,
"recovery_mode": null,
"recovery_point_id": "932606668166874635",
"restore_mode": "AlternateLocation",
"source_resource_id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SQLResourceGroup/providers/Microsoft.Compute/virtualMachines/testSQLVM",
"workload_type": "SQLDataBase",
"alternate_directory_paths": [],
}
Use this recovery configuration in the az restore restore-azurewl command. Select the --use-secondary-region flag to restore the database to the secondary region.
az backup restore restore-azurewl --resource-group SQLResourceGroup \
--vault-name testSQLVault \
--recovery-config recoveryconfig.json \
--use-secondary-region \
--output table
The output appears as:
Name Operation Status Item Name Backup Management Type Start Time UTC Duration
------------------------------------ ------------------ ---------- ------------------------- ------------------------ -------------------------------- --------------
0d863259-b0fb-4935-8736-802c6667200b CrossRegionRestore InProgress master [testSQLVM] AzureWorkload 2022-06-21T08:29:24.919138+00:00 0:00:12.372421
Note
The RPO for the backup data to be available in secondary region is 12 hours. Therefore, when you turn on CRR, the RPO for the secondary region is 12 hours + log frequency duration (that can be set to a minimum of 15 minutes).
Restore as files
To restore the backup data as files instead of a database, use RestoreAsFiles as the restore mode. Then choose the restore point, which can be a previous point-in-time or any previous restore points. Once the files are dumped to a specified path, you can take these files to any SQL machine where you want to restore them as a database. Because you can move these files to any machine, you can now restore the data across subscriptions and regions.
Here, choose the previous point-in-time 28-11-2019-09:53:00 to restore and the location to dump backup files as /home/sql/restoreasfiles on the same SQL server. You can provide this restore point in one of the following formats: dd-mm-yyyy or dd-mm-yyyy-hh:mm:ss. To choose a valid point-in-time to restore, use the az backup recoverypoint show-log-chain command, which lists the intervals of unbroken log chain backups.
With the above restore point name and the restore mode, create the recovery config object using the az backup recoveryconfig show command. Check each of the remaining parameters in this command:
- --target-container-name: The name of a SQL server that's successfully registered to a Recovery Services vault and present in the same region as per the database to be restored. Let's restore the database as files to the same SQL server that you've protected, named hxehost.
- --rp-name: For a point-in-time restore, the restore point name is DefaultRangeRecoveryPoint.
az backup recoveryconfig show --resource-group SQLResourceGroup \
--vault-name SQLVault \
--container-name VMAppContainer;Compute;SQLResourceGroup;testSQLVM \
--item-name sqldatabase;mssqlserver;master \
--restore-mode RestoreAsFiles \
--rp-name 932606668166874635 \
--target-container-name VMAppContainer;Compute;SQLResourceGroup;testSQLVM \
--filepath /sql/restoreasfiles \
--output json
The response to the query above js a recovery config object that appears as:
{
"alternate_directory_paths": null,
"container_id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SQLResourceGroup/providers/Microsoft.RecoveryServices/vaults/SQLVault/backupFabrics/Azure/protectionContainers/VMAppContainer;Compute;SQLResourceGroup;testSQLVM",
"container_uri": "VMAppContainer;compute;SQLResourceGroup;testSQLVM",
"database_name": null,
"filepath": "/sql/restoreasfiles",
"item_type": "SQL",
"item_uri": "SQLDataBase;mssqlserver;master",
"log_point_in_time": null,
"recovery_mode": "FileRecovery",
"recovery_point_id": "932606668166874635",
"restore_mode": "AlternateLocation",
"source_resource_id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SQLResourceGroup/providers/Microsoft.Compute/virtualMachines/testSQLVM",
"workload_type": "SQLDataBase"
}
Now, to restore the database as files run the az restore restore-azurewl command. To use this command, enter the JSON output above that's saved to a file named recoveryconfig.json.
az backup restore restore-azurewl --resource-group SQLResourceGroup \
--vault-name SQLVault \
--restore-config recoveryconfig.json \
--output json
The output appears as:
{
"eTag": null,
"id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SQLResourceGroup/providers/Microsoft.RecoveryServices/vaults/SQLVault/backupJobs/e9cd9e73-e3a3-425a-86a9-8dd1c500ff56",
"location": null,
"name": "e9cd9e73-e3a3-425a-86a9-8dd1c500ff56",
"properties": {
"actionsInfo": [
"1"
],
"activityId": "9e7c8ee4-f1ef-11ec-8a2c-3c52826c1a9a",
"backupManagementType": "AzureWorkload",
"duration": "0:00:04.304322",
"endTime": null,
"entityFriendlyName": "master [testSQLVM]",
"errorDetails": > [!NOTE]
> Information the user should notice even if skimmingnull,
"extendedInfo": {
"dynamicErrorMessage": null,
"propertyBag": {
"Job Type": "Restore as files"
},
"tasksList": [
{
"status": "InProgress",
"taskId": "Transfer data from vault"
}
]
},
"isUserTriggered": true,
"jobType": "AzureWorkloadJob",
"operation": "Restore",
"startTime": "2022-06-22T05:53:32.951666+00:00",
"status": "InProgress",
"workloadType": "SQLDataBase"
},
"resourceGroup": "SQLResourceGroup",
"tags": null,
"type": "Microsoft.RecoveryServices/vaults/backupJobs"
}
The response provides you with the job name. You can use this job name to track the job status using the az backup job show command.
Note
If you don't want to restore the entire chain but only a subset of files, follow the steps as documented here.
Cross Subscription Restore
With Cross Subscription Restore (CSR), you have the flexibility of restoring to any subscription and any vault under your tenant if restore permissions are available. By default, CSR is enabled on all Recovery Services vaults (existing and newly created vaults).
Note
- You can trigger Cross Subscription Restore from Recovery Services vault.
- CSR is supported only for streaming based backup and is not supported for snapshot-based backup.
- Cross Regional Restore (CRR) with CSR is not supported.
az backup vault create
Add the parameter cross-subscription-restore-state that enables you to set the CSR state of the vault during vault creation and updating.
az backup recoveryconfig show
Add the parameter --target-subscription-id that enables you to provide the target subscription as the input while triggering Cross Subscription Restore for SQL or HANA datasources.
Example:
az backup vault create -g {rg_name} -n {vault_name} -l {location} --cross-subscription-restore-state Disable
az backup recoveryconfig show --restore-mode alternateworkloadrestore --backup-management-type azureworkload -r {rp} --target-container-name {target_container} --target-item-name {target_item} --target-resource-group {target_rg} --target-server-name {target_server} --target-server-type SQLInstance --target-subscription-id {target_subscription} --target-vault-name {target_vault} --workload-type SQLDataBase --ids {source_item_id}
Next steps
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for