1.If you want to restore a encrypted backup to another server as usual you encounter the following error
Cannot find server certificate with thumbprint …...
2.Find the cert name : in this example vestacert
SELECT * FROM sys.certificates
3.backup the cert from source server (Source encryptedserver) :
BACKUP CERTIFICATE vestacert
TO FILE = 'c:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'c:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Password12#')
4.Create new Master Cert on UAT server if not already exist
USE master GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D1ffPa$$w0rd'
5.Restore backup certs in UAT server (UATserver)
CREATE CERTIFICATE vestacert2
FROM FILE = 'C:\tmp\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY (FILE = 'C:\tmp\LCMS\certificate_TDE_Test_Key.pvk',
DECRYPTION BY PASSWORD = 'Passsword12#')
6.After this step restoring backup does not have any error and all data was readable.
7.But the funny thing is that removing encryption simply and taking new backup and restoring it on final server (Final Server) does not work and gives the following error The file "mydb_log" failed to initialize correctly. Examine the error logs for more details.
8.The correct way of removing encryption from UAT is to remove all signs like below step by step and from bottom to top
USE master
ALTER DATABASE mydb SET ENCRYPTION OFF
USE mydb
DROP DATABASE ENCRYPTION KEY
USE master
DROP CERTIFICATE vestacert2
DROP MASTER KEY
9.Now create a new backup from UAT server and restore it to final server.
