How to restore Database from one Managed instance to other

Hi Mates,

Today I would like to share about restoring a database from one Managed Instance to other. We have 2 methods to achieve this.

1) Traditional way of using Azure Storage Explorer

2) Azure CLI  

unfortunately as of now we can't perform this using Azure portal

When it comes to Traditional way we need to backup the database to blob storage container. you can use command or GUI to achieve this. 

Before doing this we need to configure that and in our environment we don't have permissions hence informed to Hosting team to create one. upon getting the information we need to create credentials under security in Management Studio

After that you can trigger the backup using TSQL command or GUI like below. Keep in mind we can trigger copy-only backups  as the backups will be taken care automatically in MI

BACKUP DATABASE [XXXXXXX]
TO URL = 'https://XXXXXX.blob.core.windows.net/XXXXX/XXXX_20200424.bak'
WITH COPY_ONLY

once done you can check your backups in Azure storage Explorer where we have option of upload/Download

In this case we need to download the backup to our local server and then need to upload the backup file to another Managed instance blob container. 

After Uploading the file we need to perform the restore.

restore database [XXXXXX] from URL='https://XXXXX.blob.core.windows.net/XXXX/XXX_backup_2021_01_29_130404.bak' 

Unlike in on-premises we can't run with stats as it throws an error. Interestingly when I tried to perform restore from GUI I got the below error. Probably it might be due to permission issue

We can go with first method provided if we don't have permissions to carry out using AZ az sql midb restore.

In my Environment we don't have permissions to make use of that and I got the below error.

How to make use Azure CLI? At first install AZ CLI on your jump server. Then Launch PowerShell and run the command AZ Login which will redirect to your browser popping for user name and password.

Next we need to set subscription by running the below command. you can get the subscription details using portal or using commands

az account set --subscription "Enter Subscription ID"

After that you need to run the below command 

az sql midb restore -g Source_Resource_group --mi Source_instance_name 

-n Source_database_name   --dest-name Destination_database_name

 --time "2021-01-29T11:30:00" --dest-mi Dest_instance_name 

--dest-resource-group dest_resource_group

Just to keep in Mind that Managed instance is making use of UTC time kindly adjust to your needs.

"TIME" - managed instance is UTC time

This way we can restore the database faster .