TDE and Database Backup and Recovery

Back up the master key and certificate that are used for TDE to a safe location. The master key and certificate are required to restore backups that were taken when the database was encrypted with TDE. After you remove the database encryption key, take a log backup followed by a fresh full backup of the decrypted database.

Restore a Encrypted Database

In order to perform a successful restore, we’ll need the database master key in the master database in place and we’ll need to restore the certificate used to encrypt the database, but we’ll need to make sure we restore it with the private key. In checklist form:

There’s a database master key in the master database.
The certificate used to encrypt the database is restored along with its private key.

If this is a new server, or a secondary server, you must recreate the Master Key (with the same password) in the master database and recreate the certificate also in the master database.

USE master
GO

-- Restore Master Key. 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword01';  
GO  


USE master
GO

-- Restore Certificate
CREATE CERTIFICATE TMycertificate01   
FROM FILE = 'D:\TDE\Mycertificate01_backup'  
WITH PRIVATE KEY   
(  
    FILE = 'D:\TDE\MyMasterKey_backup',  
    DECRYPTION BY PASSWORD = 'MyPassword01'  
);  
GO

Restore TDE Encrypted Database Error:

Msg 33111, Level 16, State 3, Line 3
 Cannot find server certificate with thumbprint '0x21ECA0D989BFB9F11C8FDF3D1C52EA9AA77F769A'.
 Msg 3013, Level 16, State 1, Line 3
 RESTORE DATABASE is terminating abnormally.
 Completion time: 2020-09-04T11:45:40.7968459-07:00