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.