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'
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
Just to keep in Mind that Managed instance is making use of UTC time kindly adjust to your needs.
- managed instance is UTC time
This way we can restore the database faster .