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

Transparent Data Encryption (TDE) is a 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.

Azure SQL Database Managed Instance has automatic backups that are stored on Azure storage, fully encrypted, keeping you compliant, and providing 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 TDE protected databases that use Service managed keys in Managed Instance you cannot take a manual COPY_ONLY backup. COPY_ONLY backups might enable a person with high privilege to take your data, and violate your security policy. SQL Server allows you to take your own backups of TDE protected databases but this includes a process of exporting the protection keys, which is not allowed in Managed Instance.

NOTE:

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. As an alternative you can use the TDE Protector stored in your own Azure Key Vault, Azure’s cloud-based external key management system. If you put the key in the Azure Key Vault you can take the backups that can be decrypted after restore with your own key. However, if you delete the key, your backups cannot be decrypted anymore. 

Strict TDE protection does not allow you to take your own custom backups. If you need a backup of a TDE protected database, you would need to temporarily 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 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 another instance:

33111 Cannot find server certificate with thumbprint ...

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 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 the Log:

DBCC SHRINKFILE (  <logName>, 1)

6. Run select * from sys.dm_db_log_info. This should not show any active VLF that is encrypted by thumbprint.

7. Take the backup.

8. Restore backup and make sure it does not ask for the certificate.

If you follow this procedure, you should be able to successfully restore backups to another instance.