Restore a Copy_Only backup taken in an Azure SQL Managed Instance onto another Azure SQL Managed Instance

-> 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)

ADDITIONAL INFORMATION:
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

BUTTONS:
OK

-> 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.

Thank You,
Vivek Janakiraman

Disclaimer:
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.

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