Backup TDE protected database on Azure Managed Instance

We can take a COPY_ONLY backup of a TDE protected (encryption enabled) database on Azure SQL Managed Instance using the following steps.

1. Connect to your Azure SQL Managed Instance using SQL Server Management Studio or Azure Data Studio
2. Check if the database is encrypted with TDE-protected (encryption enabled)

SQL

Select * from sys.dm_database_encryption_keys

3. If the database is encrypted, run the following T-SQL command to disable TDE for the database and turn off encryption. Make sure there is no active transaction when performing this operation:

SQL

ALTER DATABASE [database_name] SET ENCRYPTION OFF;

Replace [database_name] with the name of the database you want to disable TDE for.

Wait for the TDE disabling operation to complete.
Note that disabling TDE will cause an interruption of service and all connections to the database will be terminated. After disabling TDE, it is recommended to backup the database and test the backup to ensure that the data can be restored if necessary.

4. Run checkpoint on the database. We can run a checkpoint on a database in Azure SQL Managed Instance using the following steps:

SQL

USE [database_name];
GO
CHECKPOINT;

Replace [database_name] with the name of the database you want to run the checkpoint on.

A checkpoint is a database operation that writes all modified data pages from the buffer cache to disk and updates the disk copy of the associated database. Checkpoints are automatically run by SQL Server, but you can also manually run a checkpoint if you need to force a database to be consistent on disk. Running a checkpoint is useful in cases where you want to ensure that the data on disk is in a consistent state, for example, before performing a backup or before running a database consistency check.

5. Drop the database encryption key (DEK): Run the following T-SQL command to drop the database encryption key.

SQL

USE [database_name];
GO
DROP DATABASE ENCRYPTION KEY;

Replace [database_name] with the name of the database you want to drop the encryption key.

Wait for the encryption key drop operation to complete.
Note that dropping the encryption key will render the encrypted data in the database unreadable and will result in data loss. It is recommended to backup the database and test the backup to ensure that the data can be restored if necessary.

6. Truncate the Log:

SQL

DBCC SHRINKFILE ( <logName>, 1)

7. Check any active VLF that is encrypted by thumbprint.

SQL

select * from sys.dm_db_log_info.

8. Create a backup of your TDE-protected database using the following command:

SQL

BACKUP DATABASE [database_name] TO URL = 'https://[your_storage_account].blob.core.windows.net/[container_name]/[database_name].bak'
WITH COPY_ONLY;

Replace [database_name] with the name of your database, [your_storage_account] with the name of your Azure storage account, and [container_name] with the name of the container where you want to store the backup.

9. Wait for the backup to complete.
Note that the COPY_ONLY option creates a standalone backup that does not affect the database’s normal backup chain and does not mark the database as having been backed up. This option is useful when you want to create a backup of the database without disrupting its normal backup schedule.

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

SQL

USE [master]
RESTORE DATABASE [dbName]
FROM URL = N'https://[storage_account].blob.core.windows.net/[container_name]/[database_test].bak'

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

Ref. https://techcommunity.microsoft.com