A certificate-protected TDE backup needs the original protecting certificate and its private key on the recovery instance. Record the protector thumbprint while the source is available, preserve the exports and their password through separate custody, and prove recovery on an independent instance. A certificate name or public certificate alone is insufficient.
This article uses SQL Server 2022 as its reference environment. It is a source-researched, untested recovery-design procedure. No certificate, key or database was exported or restored. The principal workflow concerns certificate-protected TDE; external key management requires its own provider-specific recovery design.
Understand what the database needs
TDE encrypts database data and log files as they are written to storage. A database encryption key performs that work. The key is itself protected by a certificate held by the SQL instance, or by an asymmetric key managed through an Extensible Key Management provider. The TDE documentation describes that hierarchy.
This distinction explains why a database file and its encryption key are not a complete, independently usable package. The target instance must be able to open the protector above that key. Copying the database to new storage does not remove this dependency.
For the certificate path, there are several different pieces to identify: the certificate, its private key, the protection applied to the exported private key, and the target instance’s master-key arrangement. The password used to protect an exported key is not interchangeable with a Windows password or the application’s SQL login password.
A public certificate alone is insufficient. It can identify a certificate without providing the private material needed to decrypt the database encryption key. Ask for evidence that the export includes the required private key, rather than accepting a filename ending in .cer as proof of recoverability.
Keep TDE distinct from encrypted backup sets, TLS connections and storage-volume encryption. These controls can coexist, but they protect different boundaries and may have different keys. This article does not claim that satisfying the TDE requirement satisfies every other encryption dependency.
Build a recovery dependency register
Create a private register containing database identity, source instance, SQL build, protection type, certificate identifier, export location and responsible custodian. Record the intended recovery destination and the access path that remains available if the source server is lost.
The register should reference an approved secret store rather than contain passwords or private-key contents. Separate custody matters because a person able to obtain both an encrypted backup and the usable keys may be able to recover the data outside its normal application controls.
For each retained backup period, establish which protector material is required. A key change today does not justify deleting older material still needed by retained backups. The lifetime of recovery dependencies must follow the lifetime of the protected recovery sets.
Microsoft also warns that disabling TDE does not immediately remove every need for the protecting certificate. Parts of the transaction log may remain protected, and some operations can still require the certificate until a full database backup has been taken S1. Have the DBA assess the actual retained history before retiring anything.
The dependency register is an editorial control, not a built-in discovery guarantee. Someone must reconcile it with SQL configuration and the backup catalogue. A neat register that points to an inaccessible vault is still an incomplete recovery plan.
Record the actual protector thumbprint
Untested, read-only SQL Server 2022 inventory on the surviving source or an authorised known-good copy. The encryption DMV requires VIEW SERVER SECURITY STATE on 2022; certificate metadata is also permission-filtered. Missing rows or a NULL join are not proof that no protector exists R1, R2.
SELECT DB_NAME(k.database_id) AS database_name,
k.encryption_state_desc, k.encryptor_type,
k.encryptor_thumbprint,
c.name AS certificate_name, c.thumbprint,
c.pvt_key_last_backup_date
FROM sys.dm_database_encryption_keys AS k
LEFT JOIN master.sys.certificates AS c
ON c.thumbprint = k.encryptor_thumbprint
WHERE k.database_id = DB_ID(N'LabOrders');
For certificate protection, put the thumbprint in the recovery register and match it on the destination. The last-export date is a lead, not proof that the private-key export and its password still exist. An asymmetric/EKM protector does not match this certificate-only path. If the source has already been lost, use the previously captured register, backup/error evidence and authorised protector inventory; this query cannot reconstruct a lost private key.
Microsoft says to back up the certificate and private key immediately after enabling TDE S1. Capture new protector material when it changes, while retaining old protectors needed by retained backups. The DBA's change procedure uses BACKUP CERTIFICATE ... WITH PRIVATE KEY and the corresponding CREATE CERTIFICATE ... FROM FILE import, or the documented 2022+ PFX forms S2, S3; no secret-bearing script is supplied here.
Verify the export method and its scope
The certificate backup reference distinguishes exporting a certificate from exporting the associated private key. It also documents the permissions and file-access consequences. The required access should be assigned through the organisation’s established DBA process, not by granting broad rights to bypass an export error.
SQL Server 2022 adds documented support for certificate/private-key backup and restore using PFX format. That is a version-specific capability, not permission to apply the same syntax to an older instance. Confirm the actual engine version and the export/import format on both ends before designing the rehearsal.
The exported files are written under the SQL Server service context described by the source. Access can therefore differ from what the interactive administrator sees. If the recovery instance uses a different service identity, the destination file permissions must be reviewed for that identity. Avoid broad access grants to an entire backup directory.
Do not place literal passwords in an article, saved terminal transcript or shared runbook. Have the DBA use the approved secure execution mechanism and retain only non-secret evidence: certificate identity, export success, selected format, controlled file location and custody reference.
This guide withholds executable certificate-export, import and key-creation statements. They change security state or produce sensitive material. The official examples are syntax references for a specialist-reviewed procedure, not a script to paste into an unspecified server.
Prepare a genuinely separate recovery instance
Use an isolated SQL instance with an explicitly supported version and sufficient database capacity. It should not silently inherit the original server’s certificate store or master database. Otherwise a successful restore may test access to an existing protector rather than recovery of the protector itself.
Identify what the rehearsal intentionally removes. For example, the source database server can be unavailable while the approved recovery vault and backup destination remain accessible. Document that scenario. Do not describe the result as recovery without identity infrastructure if the test operator still depends on the production domain.
Check the destination’s current master-key state before planning any creation or import. A new key must not overwrite or undermine existing protected objects. Microsoft’s TDE movement example explains how the destination certificate is recreated from the original certificate and private-key backup. Its example also includes detach/attach operations, which are not the recommended starting point for this backup rehearsal.
Use an independent database backup and a unique destination. Do not detach the live source merely to demonstrate key recovery. Do not restore the original instance’s entire master database as an improvised shortcut to make one user database decrypt.
Rehearse the dependency sequence
Have the recovery operator first obtain the approved backup and key material through the access paths that the real incident permits. Record whether access depended on the original administrator or on an undocumented exception.
Next, have the DBA validate the destination’s master-key arrangement and import the original certificate with its private key using the supported procedure. Confirm certificate identity against the register. Matching a display name alone is not sufficient: another certificate can be given the same friendly name without being the same protector.
Only after the protector is available should the DBA execute the separately reviewed database restore. Keep data-file destinations, database naming and external integrations isolated. The SQL restore-chain article covers selecting full, differential and log backups; it is not repeated here.
If the restore reports a missing certificate or cannot open a key, preserve the exact error. Investigate certificate identity, private-key inclusion, export password custody, service-account access and target key state in that order of dependency. Do not repeatedly create replacement certificates with the same name and expect them to decrypt the original data.
If an external key provider is involved, stop the certificate-only procedure. Provider installation, authentication, key availability and supported recovery semantics must be established with that provider. An exported SQL certificate cannot be assumed to replace an external asymmetric key.
Verify more than successful decryption
An online database establishes that recovery passed an important engine boundary. It does not establish that the application’s records are correct, users can authenticate, or scheduled work is safe to resume.
Have the DBA perform the agreed integrity assessment. Validate known synthetic records through the application and exercise a representative authorised operation. Record the selected backup point and any expected missing later work. Keep external payments, mail and integrations disabled or redirected during the isolated test.
Record access time, key preparation, database restoration and application acceptance separately. If the database restores quickly but obtaining the key requires an unavailable individual, the recovery design still has an operational weakness.
Finally, demonstrate that future protection can continue from the accepted recovery environment. Record the new instance’s protector custody without deleting older exports. Recovered data must not become the next unprotected dependency.
Avoid misleading certificate housekeeping
TDE certificates do not behave like browser certificates in every respect. Microsoft states that an expired certificate can still be used to encrypt and decrypt TDE data S1. Do not delete an old TDE certificate merely because a generic certificate dashboard marks its expiry date red.
Likewise, rotating a protector is not proof that old recovery sets no longer need old material. Retiring keys requires an explicit dependency and retention assessment. This article supplies no key deletion or rotation procedure.
Close the rehearsal with a factual statement: which backup and protector were recovered, on which separate instance, by which authorised role, and which application checks passed. Keep unavailable dependencies visible. A usable recovery set consists of data, keys, access and a demonstrated procedure.
Limitations
This procedure targets certificate-protected TDE on SQL Server 2022. Earlier releases need their documented export/import format; 2025 compatibility is not tested here. Backup encryption and EKM can add different protectors, and metadata inspection cannot prove key custody or recoverability.
Acceptance checklist
- Database and protector thumbprint recorded, not just a friendly name.
- Private-key export and approved password custody independently accessible.
- Recovery operator opened the database on an isolated separate instance.
- Application checks passed and old protectors remain available for retained backups.
Learning notes
- A certificate export can omit the private key that recovery needs.
- The original server’s loss must not also remove the only usable key copy.
- Successful decryption is one stage of application recovery, not its final acceptance.
Related reading: SQL Point-in-Time Restore: Plan and Verify the Backup Chain; Verify a Backup Delivered to S3 or SFTP Before Recovery; The Database Restored, but the Application Cannot Log In.
Sources and review status
Sources accessed 30 October 2026. Exact displayed dates, scope and claim mapping are in the accompanying evidence file. Revised after independent review; independent recheck pending. All examples remain untested and no lab validation is claimed.
- Transparent Data Encryption (TDE) - SQL Server | Microsoft Learn
- Move a TDE-Protected Database to Another SQL Server - SQL Server | Microsoft Learn
- BACKUP CERTIFICATE (Transact-SQL) - SQL Server | Microsoft Learn
Additional revision sources: