Back up the TDE certificate, or you cannot read your backups

Transparent data encryption (TDE) encrypts the database files and, with them, every backup of the database. The key that protects it sits in a certificate in the master database of the instance. A backup restored on another server, or read by a tool, is only readable with that certificate and its private key. Without them the backup is a file of encrypted noise, however valid it is. This article shows how to find the certificate, back it up, and use it.

Find the certificate of a database

On the instance that has the database:

SELECT DB_NAME(k.database_id) AS database_name,
       c.name AS certificate_name,
       c.thumbprint,
       c.expiry_date
FROM sys.dm_database_encryption_keys AS k
JOIN master.sys.certificates AS c ON c.thumbprint = k.encryptor_thumbprint
WHERE k.encryptor_type = 'CERTIFICATE';

The thumbprint matters: it is how a backup names the certificate it needs. For a backup file, RESTORE FILELISTONLY shows it in the TDEThumbprint column, so you can tell which certificate is required before you start. The columns of FILELISTONLY.

Back up the certificate and its private key

USE master;
BACKUP CERTIFICATE TdeCert
TO FILE = N'D:\Keys\TdeCert.cer'
WITH PRIVATE KEY (
    FILE = N'D:\Keys\TdeCert.pvk',
    ENCRYPTION BY PASSWORD = N'a long password that you keep in your password manager');

This writes two files: the certificate (.cer) and its private key (.pvk), protected by the password. Keep all three, the two files and the password, somewhere other than the server, and test that you can use them. A certificate that exists only on the server it protects disappears with that server. SQL Server itself warns about this when you enable TDE.

Restore the certificate on another server

On the instance where the backup has to be restored, once:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'another long password';  -- only if master has none yet

CREATE CERTIFICATE TdeCert
FROM FILE = N'D:\Keys\TdeCert.cer'
WITH PRIVATE KEY (
    FILE = N'D:\Keys\TdeCert.pvk',
    DECRYPTION BY PASSWORD = N'a long password that you keep in your password manager');

After that RESTORE DATABASE works as it does for any backup.

Read the backup without a server

SQLTreeo Restore opens encrypted backups on a computer without SQL Server, with the same two files:

1. Open the backup. It names the certificate it needs, by thumbprint.
2. Choose Add certificate… in the warning, pick the .cer and .pvk files and enter the password. The certificates are listed under Settings › Certificates, and the password is stored encrypted for your Windows account.
3. The backup opens again, with its tables and rows.

This works for backups of TDE databases and for backups made WITH ENCRYPTION (AES), compressed or not. A wrong password, or a private key file that does not belong to the certificate, is said plainly. Backups encrypted with TRIPLE_DES_3KEY are not supported.

Related: How to open a .bak file without SQL Server.