You will receive the below error message when you try to restore a TDE encrypted database.



Step 1: Backup the Existing certificate in Source Server

1.  Login to the Source server and execute the below T-SQL and make a note of the Certificate ‘name’.

select * FROM [master].[sys].[certificates]

2.  Backup the Certificate to disk.

BACKUP CERTIFICATE CertificateName TO FILE = '<Path>\cert.cer' WITH PRIVATE KEY ( FILE = '<Path>\key.p7b', ENCRYPTION BY PASSWORD = 'P@ssw0rd');

3.  Copy the Exported Certificate files to the destination server.


Step 2: Restore the certificate in destination server.

1.  Login to the destination server and execute the below T-SQL.

USE master;




CREATE CERTIFICATE CertificateName FROM FILE = '<Path>\cert.cer' WITH PRIVATE KEY (FILE = '<Path>\key.p7b', DECRYPTION BY PASSWORD = 'P@ssw0rd');


Step 3: Restore the Databases


Step 4: Verify the Encryption Status

1.  Execute the below query in Destination server and check whether the databases appear in the list.

         SELECT FROM sys.dm_database_encryption_keys

       WHERE encryption_state = 3;

 *The value 3 represents an encrypted state on the database and transaction logs.