Find if backup is encrypted or not.

Recently I got a requirement to perform the  backup of SQL Server database and provide confirmation that backup is encrypted.
 

There are two ways through which database backup be encrypted.

1. If TDE is enabled on the database, database backup gets automatically encrypted through TDE encryption key and database cannot be restored through backup file without database encryption key.  https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16
2. Through database backup encryption. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-ver16

If you do not know if a backup file is encrypted or not, you can follow steps to confirm the same.

1. By executing “RESTORE FILELISTONLY” , you will get a second last  column named as ‘TDEThumbprint’ . If this column is not null for the data files, backup is encrypted by TDE.
2. If backup file is not encrypted by TDE, it may be encrypted by backup encryption. This can be confirmed using “RESTORE HEADERONLY” command against backup file. It will have a non-null value against third last column ‘EncryptorThumbprint’.

Hope this helps !!

Happy learning!!