Restore a Managed Instance database to another geo-region using the Azure CLI

Applies to: Azure SQL Managed Instance

This Azure CLI script example restores an Azure SQL Managed Instance database from a remote geo-region (geo-restore) to a point in time.

This sample requires an existing pair of managed instances, see Use Azure CLI to create an Azure SQL Managed Instance to create a pair of managed instances in different regions.

If you don't have an Azure subscription, create an Azure free account before you begin.

Prerequisites

Sample script

For this script, use Azure CLI locally as it takes too long to run in Cloud Shell.

Sign in to Azure

Use the following script to sign in using a specific subscription.

subscription="<subscriptionId>" # add subscription here

az account set -s $subscription # ...or use 'az login'

For more information, see set active subscription or log in interactively

Run the script

# Restore a Managed Instance database to another geo-region
# Use Bash rather than Cloud Shell due to its timeout at 20 minutes when no interactive activity 
# In Windows, run Bash in a Docker container to sync time zones between Azure and Bash.

# Run this script after running the script in https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/scripts/create-configure-managed-instance-cli twice to create two managed instances
# Provide the values for these three variables before running this rest of this script

# Variable block for additional parameter values
$instance = "<msdocs-azuresql-instance>" # add instance here
$targetInstance = "<msdocs-azuresql-target-instance>" # add target instance here
$resourceGroup = "<msdocs-azuresql-rg>" # add resource here

let "randomIdentifier=$RANDOM*$RANDOM"
$managedDatabase = "managedDatabase-$randomIdentifier"

echo "Creating $($managedDatabase) on $($instance)..."
az sql midb create -g $resourceGroup --mi $instance -n $managedDatabase

# Sleeping commands to wait long enough for automatic backup to be created
echo "Sleeping..."
sleep 40m

# To specify a specific point-in-time (in UTC) to restore from, use the ISO8601 format:
# restorePoint=ā€œ2021-07-09T13:10:00Zā€
restorePoint=$(date +%s)
restorePoint=$(expr $restorePoint - 60)
restorePoint=$(date -d @$restorePoint +"%Y-%m-%dT%T")
echo $restorePoint

echo "Restoring $($managedDatabase) to $($targetInstance)..."
az sql midb restore -g $resourceGroup --mi $instance -n $managedDatabase --dest-name $targetInstance --time $restorePoint

Clean up resources

Use the following command to remove the resource group and all resources associated with it using the az group delete command - unless you have an ongoing need for these resources. Some of these resources may take a while to create, as well as to delete.

az group delete --name $resourceGroup

Sample reference

This script uses the following commands. Each command in the table links to command specific documentation.

Script Description
az sql midb Managed Instance Database commands.

Next steps

For more information on Azure CLI, see Azure CLI documentation.

Additional SQL Database CLI script samples can be found in the Azure SQL Database documentation.