Restore Database Master Key and Certificate

Database Restore Without Valid Certificate

Database Restore of backup file will fail without valid certificate.

-- Restore without Certificate
RESTORE DATABASE TestEncryptedDBWithoutCertificate
  FROM DISK = N'D:\SQLServerBackups\TestEncryptedDB\TestEncrypedDB_Full.bak'
  WITH MOVE 'TestEncryptedDB' TO N'D:\MSSQL\TestEncryptedDB02\TestEncryptedDBWithoutCertificate.mdf',
       MOVE 'TestEncryptedDB_log' TO N'D:\MSSQL\TestEncryptedDB02\TestEncryptedDBWithoutCertificate_log.ldf';
GO

Msg 33111, Level 16, State 3, Line 60
Cannot find server certificate with thumbprint ‘0xCC4472D721418B759E36A04034F0C2A95EE68DD6’.
Msg 3013, Level 16, State 1, Line 60
RESTORE DATABASE is terminating abnormally.

Restore Master Key and Certificate

You also need to restore the Master Key in addition to restore the certificate, or you will get an error.

Msg 15507, Level 16, State 30, Line 60
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 60
RESTORE DATABASE is terminating abnormally.

If this is a new server or a secondary server, make sure to create a new master key with the same password in the master database. Afterwards, restore the certificate.

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