How to backup a TDE protected database on Azure SQL Managed Instance

How to backup a TDE protected database on Azure SQL Managed Instance

Spread the love

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)

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:

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:

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.

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:

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

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

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.

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

Ref. https://techcommunity.microsoft.com


Spread the love
Author: OTi
Meet OTi, the brains behind the popular tech blog "OurTechIdeas.com." OTi is an accomplished author, tech enthusiast, and an expert in SQL Server. With over a decade of experience in the tech industry, he has become a trusted voice in the world of database management. OTi's passion for technology began at a young age, and he has been tinkering with computers ever since. He graduated with a degree in Computer Science and has since worked for some of the biggest tech companies in the world. His experience has given him a deep understanding of how technology works and how it can be used to improve our lives.

Leave a Reply

Your email address will not be published. Required fields are marked *