How to find if SQL server backup is encrypted with TDE without restoring the backup

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.

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.

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
)
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = '''+@backupFile+'''')


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