/* PROBLEM - You need to decrypt the database now that the one on the target server has been obfuscated but is still encrypted */ /* is the database protected by the certificate? */ Select db_name(ek.database_id)dbName,* from sys.dm_database_encryption_keys ek join sys.certificates cr on ek.encryptor_thumbprint=cr.thumbprint /* if the db is certificate protected and you have the cert password, you can turn it off */ USE Master GO ALTER DATABASE CLEANDB SET ENCRYPTION OFF USE CLEANDB GO DROP DATABASE ENCRYPTION KEY GO USE master GO DROP CERTIFICATE RESTORE_TDE GO /* Should be gone now */ Select db_name(ek.database_id)dbName,* from sys.dm_database_encryption_keys ek join sys.certificates cr on ek.encryptor_thumbprint=cr.thumbprint /* Back it up now without the certificate */ BACKUP DATABASE [CLEANDB] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\CLEANDB\CLEANDB_Unencrypted_JUN_24_21.bak' WITH NOFORMAT, NOINIT, NAME = N'CLEANDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
An issue that presents itself here: these steps are necessary for each iteration of the clean database brought in. Steps are -
- Enroll the source certificate on the target server
- Restore the encrypted (obfuscated - clean) database to target
- Remove the encryption using the steps above
- Back up the database to be exported to another target like development or another test database
That doesn't really solve the problem. The master key can only be dropped if it isn't already encrypting a database.