You may have unexpected scenario in your PRODUCTION environment that caused by local issue. Below 3 scenarios are just testing results in our lab. Note: Please ensure we have valid backup for all related files before you make any changes as discussed below.

We'd like to address the importance of keeping valid backups of these files in case of disaster:Ģ) password to the private key of the TDE certificate ģ) password to decrypt the master key in master database Ĥ) healthy backup of system and user databases. In these situations, we are missing part of the important files on encryption hierarchy to restore the chain. We will discuss the AKV and 3rd party EKM/HSM scenario in another blog.īelow are 3 common scenarios where DEK is encrypted by certificate stored on master database. Below is the encryption hierarchy when we use native SQL Server certificate stored on master database for TDE database. The key point in successfully restoring a TDE-enabled database is the ability to fix the encryption hierarchy by recovering all dots on the chain (i.e., machine account, service master key, master key of master database, certificate, database encryption key). You only have backup files for a few remaining components of your TDE databases and you need to save your production database immediately. For example, you have lost the backup files for your TDE certificate and your master database is corrupted. In unexpected scenarios, you may encounter challenges in recovering your TDE-enabled databases. Summary: This article discusses 3 common scenarios where you can and cannot recover your TDE-enabled database that using native SQL certificate stored on master database to encrypt the TDE (not using AKV or 3rd-party EKM/HSM).

Restoring master database in sql server