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

Solution:

 

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;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';

GO

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;
       GO

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