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?



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.

  • Thank You all for a quick response and @ScottHodgin yes I wanted to know if the backup is from a TDE database and Brent's answer made it clear. – yegnasew Apr 1 '19 at 18:43
  • @Brent Ozar: In both cases, I would want the query to return, "Partially encrypted." Yes, this means having a 3-state property instead of a boolean. Obviously such a property is not really feasible unless Microsoft implements it. – Brian Apr 8 '19 at 21:50
  • @Brian bingo. It's not feasible given the current state. – Brent Ozar Apr 9 '19 at 6:54

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.


Extending Scott's Answer, here is the SQL Query that will tell you if a backup is encrypted or not.

Declare @backupFile varchar(max) = 'J:\backups\psa20191029.bak'

 DECLARE @fileListTable TABLE (
    [LogicalName]           NVARCHAR(128),
    [PhysicalName]          NVARCHAR(260),
    [Type]                  CHAR(1),
    [FileGroupName]         NVARCHAR(128),
    [Size]                  NUMERIC(20,0),
    [MaxSize]               NUMERIC(20,0),
    [FileID]                BIGINT,
    [CreateLSN]             NUMERIC(25,0),
    [DropLSN]               NUMERIC(25,0),
    [UniqueID]              UNIQUEIDENTIFIER,
    [ReadOnlyLSN]           NUMERIC(25,0),
    [ReadWriteLSN]          NUMERIC(25,0),
    [BackupSizeInBytes]     BIGINT,
    [SourceBlockSize]       INT,
    [FileGroupID]           INT,
    [LogGroupGUID]          UNIQUEIDENTIFIER,
    [DifferentialBaseLSN]   NUMERIC(25,0),
    [DifferentialBaseGUID]  UNIQUEIDENTIFIER,
    [IsReadOnly]            BIT,
    [IsPresent]             BIT,
    [TDEThumbprint]         VARBINARY(32) -- remove this column if using SQL 2005

select distinct LogicalName + case when TDEThumbprint is null then ' is not encrypted'
                                    else ' is encrypted'
                                    end as AmIEncrypted
from @fileListTable
where type='D'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.