Transparent Data Encryption (TDE) is security feature in Azure SQL Database (Single Database and Managed Instance) that transparently encrypts data pages when they are exchanged between memory and underlying storage.
One of the current constraints in TDE protected databases in Managed Instance is that you cannot take the manual COPY_ONLY backups of the TDE protected database. If you need a backup of a database, you would need to temporary disable TDE, take a backup, and then enable TDE again. However, even in this case you would need to ensure that there are no encrypted pages in the database and wait for decryption to finish before you take a backup. If you backup the database before decryption finishes, you might get the following error when you try to restore the backup on other instance:
33111 Cannot find server certificate with thumbprint …
NOTE: The recommended way to backup and restore databases in Managed Instance is using built-in automatic backups and cross-instance point-in-time restore. However, if you need to use the manual backups, here is the procedure that you would need to follow:
1. Check if the db is encrypted with TDE:
Select * from sys.dm_database_encryption_keys
2. If the db is encrypted, alter the db to turn off encryption. Make sure there is no active transaction when performing this operation.
Alter database <dbName> set encryption Off
3. Run checkpoint on the db
Checkpoint
4. Drop the database encryption key (DEK)
DROP DATABASE ENCRYPTION KEY
5. Truncate Log
DBCC SHRINKFILE ( <logName>, 1)
6. Run select * from sys.dm_db_log_info and this should not show any active VLF that is encrypted by thumbprint
7. Take the backup
8. Restore backup and make sure it doesn’t ask for the certificate.
If you follow this procedure, you would be able to successfully restore backups to other instance.