Prove encrypted SQL Server backups can survive server loss

A backup file can be intact and still be unusable on a replacement server. When encryption depends on a certificate whose private key exists only on the failed instance, copying the backup file is not enough. Recovery planning must include the cryptographic material and a tested way to make it usable elsewhere.

Identify what protects the backup

Distinguish explicit backup encryption from Transparent Data Encryption. Explicit backup encryption protects the backup using its configured encryptor. TDE protects the database through a database encryption key, whose protector must be available when moving or restoring that database. A recovery chain may involve both features, and the required protectors should be inventoried rather than guessed from filenames.

For certificate-based protection, inspect certificates in master and record thumbprints alongside the backup inventory. The following query is an inventory starting point, not proof that every certificate is needed by every backup.

USE master;
SELECT name, thumbprint, expiry_date,
       pvt_key_encryption_type_desc
FROM sys.certificates
WHERE name NOT LIKE '##%'
ORDER BY name;

Match actual backup or restore metadata to the relevant encryptor. A certificate's friendly name can differ between servers; the matching cryptographic identity matters. If an asymmetric key or an external key-management provider is involved, recovery requires that provider's documented setup and access path rather than the certificate-file recipe below.

Do not assume a backup of the user database contains everything necessary to bootstrap its decryption on a new instance. The replacement environment needs the appropriate protector and private-key access before the encrypted restore can proceed.

Export both certificate and private key

The following is an operational template for an existing certificate named BackupRecoveryDemo. Replace the certificate name, paths, and secret with approved values. The directory must already exist and be accessible to the SQL Server service. The example does not create an encryptor or change any backup job.

USE master;
BACKUP CERTIFICATE [BackupRecoveryDemo]
TO FILE = 'D:\KeyExport\BackupRecoveryDemo.cer'
WITH PRIVATE KEY
(
    FILE = 'D:\KeyExport\BackupRecoveryDemo.pvk',
    ENCRYPTION BY PASSWORD = 'REPLACE_WITH_A_UNIQUE_SECRET'
);

The .cer file alone is not sufficient for recovery that requires the private key. Preserve the encrypted private-key file and its export password through controlled, independent recovery channels. Keeping the only password in a database protected by the same missing key creates a circular dependency.

The import template runs on the recovery instance in master. It assumes that a database master key already exists there and is usable by the authorized operator; prepare that prerequisite explicitly when it does not. Do not blindly create or replace an existing master key.

USE master;
CREATE CERTIFICATE [BackupRecoveryDemo]
FROM FILE = 'D:\KeyImport\BackupRecoveryDemo.cer'
WITH PRIVATE KEY
(
    FILE = 'D:\KeyImport\BackupRecoveryDemo.pvk',
    DECRYPTION BY PASSWORD = 'REPLACE_WITH_A_UNIQUE_SECRET'
);

The export password decrypts the private-key file during import. It is not the SQL login password or necessarily the destination master-key password. Distinguish these secrets in the runbook so an operator does not have to infer their roles during an outage.

After import, verify the certificate thumbprint and private-key availability. Remove temporary working copies according to the operational procedure while preserving the durable recovery copies. Do not publish real key material or passwords in tickets, source control, or restore transcripts.

Test a clean recovery path and retain old protectors

A restore rehearsal on the original instance can succeed because the required certificate is already installed. Test on an isolated replacement instance whose key inventory is known. Restore the intended full, differential, and log sequence to the required point, then run database consistency checks and application smoke checks.

Record the backup files, protector thumbprints, secret-retrieval steps, operator permissions, elapsed recovery time, and final recovered point. A successful RESTORE VERIFYONLY is useful evidence about the backup, but it is not a substitute for a completed restore and a usable database.

Key rotation does not remove the need to decrypt retained historical backups. Keep old protectors for as long as any required recovery chain depends on them. A new full backup protected by a new certificate does not magically rewrite older files or every log backup already retained.

Test loss scenarios that affect more than the database host: unavailable secret storage, a missing private-key password, and recovery by a different authorized operator. The goal is not to collect more files without purpose. It is to prove that the organization can reconstruct the complete decryption path when the original server and its local state are gone.

Technical references: Microsoft Learn: BACKUP CERTIFICATE · Microsoft Learn: Backup encryption · Microsoft Learn: Moving a TDE database.