Doing a cross-instance restore in Managed Instance

The database move from production to lower environment is the most common request a DBA can receive, when it comes to MI it could be easy or difficult depending upon your environment.

Easy when you have configured the MI with Service-managed key and all the different instances are in the same subscription. And difficult in the case when using Customer-managed key and even more difficult when restoring to a MI in another subscription.

In this blog let’s see how to do the database refresh into a different instance.

There could be two approaches into it,
1. Disable the TDE for database.
2. Do a copy_only backup into a blob.
3. Restore this database to another instance.

For doing the above you’ll need to first create a certificate in MI for the Storage account, use the below script to do it.

CREATE CREDENTIAL [https://<yourstoragename>.blob.core.windows.net/<containername>] 
WITH IDENTITY='SHARED ACCESS SIGNATURE' 
, SECRET = '<SAS token generated (without ?)>'; 

The second and better approach is to leverage Azure Portal and go to the target MI and click New Database.

Here you can select to have existing data from a backup as below:

Select the database which you want to restore click Create and you are done.

This is why I love Azure PaaS service.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s