I need to move databases having TDE enabled to another instance and re-enable the TDE with a different certificate. Generally, this would not be a problem but I seem to have problems if the databases were in AlwaysOn Availability Group environment.
Steps I would normally follow would be:
- Remove database from Availability Group.
- Set encryption off for the database.
- Drop private key from the database.
- Take full and transaction log backup.
- Set the database to simple recovery mode.
- Shrink the transaction log file.
Here is the problem I faced with.
After enabling TDE for the database in the new SQL instance with a different certificate the backup fails with the error
Cannot find server certificate with thumbprint '0x***********************'.
I believe the reason is that there is a VLF (Virtual Log File) that is marked as active (status 2) and needs to be backed up. Step 4-5 are not resolving this. This VLF seems to be related to the Availability Group and is kept there for the secondary replica is my guess.
How can I clear the VLF status and shrink the log file?