Take a COPY_ONLY backup of TDE protected database on Azure SQL Managed Instance

Transparent Data Encryption (TDE) is security feature in Azure (Single Database and Managed Instance) that transparently encrypts data pages when they are exchanged between memory and underlying .

Managed Instance has automatic backups that are stored on Azure , fully , keep you compliant, and provide most of the functionalities that you would need. Managed Instance also enables you to take your own COPY_ONLY backups that have limited usability compared to built-in automatic backups, but in some cases they might be useful (for example to keep backups that will not expire after backup retention period).

However, for the TDE protected databases in Managed Instance you cannot take the manual COPY_ONLY backups. COPY_ONLY backups might enable a person with high privilege to take your data, and violate your security policy, allows you to take your own backups of TDE protected databases but this included a proces of exporting the protection keys, which is not allowed in Managed Instance.

We are recommending to rely on automatic backups only, with the build-in restore functionality to restore a database from a point-in-time, restore a database to another instance (for instance from production to dev)or Geo-restore functionalities to move your database. These automatic backups can be kept up to 35 days. These built-in automatic backups are secure and enables you to be fully compliant. In this scenario COPY_ONLY backups are only in some specific cases.

Strict TDE protection don't allow you to take your own custom backups. If you need a backup of a TDE protected database, you would need to temporary disable TDE, take a backup, and then enable TDE again.

In some cases, you might use built-in cross-instance restore to create a copy of the database on another instance and then turn-off the TDE there to make your original instance compliant.

However, even in this case you would need to ensure that there are no 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:

33111Cannot 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 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  set encryption Off

3. Run checkpoint on the db


4. Drop the database encryption key (DEK) 


5. Truncate Log


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.


This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.