-> I am trying to restore a Copy_Only database backup taken on Azure SQL Managed Instance JBMI-Pub to another Azure SQL Managed instance JBMI-Sub.
-> Please check this article on how to perform Copy_Only backup on an Azure SQL Managed Instance.
-> The copy_only backup for Managed Instance JBMI-Pub is placed on a storage account. Lets try a restore on Managed Instance JBMI-Sub as below,
-> Sign into the Azure portal after clicking Add,
TITLE: Microsoft SQL Server Management Studio
Restore of database ‘JBDB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
System.Data.SqlClient.SqlError: Cannot find server asymmetric key with thumbprint ‘0xB48BED6BFEC0D4E9E8B1E9542A50F5225B6F6045’. (Microsoft.SqlServer.SmoExtended)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.41011.9+(SqlManagementObjects-master-APPLOCAL)&LinkId=20476
-> Tried executing the restore command as a TSQL and got same error as below,
Msg 33111, Level 16, State 4, Line 2
Cannot find server asymmetric key with thumbprint ‘0xB48BED6BFEC0D4E9E8B1E9542A50F5225B6F6045’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
-> The restore failure is due to the fact that Transparent Data Encryption (TDE) is enabled on Managed Instances JBMI-Pub and JBMI-Sub with different keys.
-> Lets change the TDE option for Managed Instance JBMI-Sub where we are restoring the backup from Service-Managed key to Customer-managed key and use the same key as Managed Instance JBMI-Pub from where copy_only backup was performed.
-> Make sure we select the same key vault, key and version for Managed Instance JBMI-Sub which is same as JBMI-Pub.
-> Click Save and start the restore again,
-> Restore completed fine this time.
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
Perfect, thank you for sharing. I first need to switch over from ‘Service-managed key’ to ‘Customer-managed key’, once done, will follow both articles steps. Thanks again. FYI, current Prod MI is not ‘Live’ yet so will work this out before we cutover.
Glad it helped Daniel. Good luck.