Ttykuu

Is there a way to find from the SQL Server Backup file or MSDB tables if the backup is encrypted with TDE without trying to restore the backup file?

Thanks

Imagine for a second that you've got a 1 terabyte database. Backing it up takes a while, and encrypting it takes a while. So imagine that:

• 9:00 AM - you start taking a full backup


• 9:01 AM - in another window, you start enabling TDE on the database


• 9:05 AM - the backup completes


• 9:10 AM - TDE completes

What would you expect your query to return, given that as soon as you finish restoring the full backup, it's going to continue applying TDE, encrypting the rest of your database?

Conversely, imagine that you start with an already-encrypted database, and:

• 9:00 AM - you remove TDE (which takes some time)


• 9:01 AM - you start a full backup


• 9:05 AM - the data pages are no longer encrypted


• 9:06 AM - your full backup completes

What would you expect the query to return? These are example scenarios of why TDE encryption isn't one of the fields included in msdb.dbo.backupset.

I up-voted Brent's answer, as his scenario could definitely muddy the water on whether the backup contained TDE data.

However, if you've had TDE enabled for a while, it seems that RESTORE FILELISTONLY (Transact-SQL) might provide the information you're after. There is a column on the result set called TDEThumbprint which "Shows the thumbprint of the Database Encryption Key. The encryptor thumbprint is a SHA-1 hash of the certificate with which the key is encrypted."

I looked at some of my backups which were both TDE encrypted and not TDE encrypted.

The backups of my TDE databases had the certificate thumbprint in that column and the backups that did not have TDE databases had null.