TDE: Backing Up the Certificate

October 12, 2026
| No Comments

With Transparent Data Encryption, a backup can only be restored on a server that has the certificate and its private key. If the certificate is lost, every backup of that database is unreadable.

Step 1: Which databases use TDE, and which certificate

SELECT DB_NAME(dek.database_id) AS database_name,
       dek.encryption_state,          -- 3 = encrypted
       c.name AS certificate_name, c.expiry_date,
       c.pvt_key_last_backup_date
FROM sys.dm_database_encryption_keys dek
LEFT JOIN master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint;

pvt_key_last_backup_date is NULL if the certificate’s private key has never been backed up.

Step 2: Back up the certificate and private key

USE master;
BACKUP CERTIFICATE [TDE_Cert]
TO FILE = N'\\SecureShare\TDE\TDE_Cert.cer'
WITH PRIVATE KEY (
    FILE = N'\\SecureShare\TDE\TDE_Cert.pvk',
    ENCRYPTION BY PASSWORD = N'<your password here>');
  • Keep the files off the server, and keep the password in the password manager, separate from the files.
  • Record which certificate protects which database.
  • Back up again whenever a certificate is created or rotated.
  • Back up the database master key in master as well:
USE master;
BACKUP MASTER KEY
TO FILE = N'\\SecureShare\TDE\master_dmk.key'
ENCRYPTION BY PASSWORD = N'<your password here>';

Step 3: Restore the certificate on another server

USE master;
-- master needs a database master key first, if it doesn't have one:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<your password here>';
CREATE CERTIFICATE [TDE_Cert]
FROM FILE = N'\\SecureShare\TDE\TDE_Cert.cer'
WITH PRIVATE KEY (
    FILE = N'\\SecureShare\TDE\TDE_Cert.pvk',
    DECRYPTION BY PASSWORD = N'<your password here>');

Do this on every availability group replica and DR server before it’s needed, not during an incident. An expired certificate still works for TDE, but rotate it in line with security policy.

From my SQL Server DBA handbook.

Setup