How to verify if a backup "*.bak" is encrypted?

Question

Is there a way to verify if the sql server backup file is encrypted or not? I have a script to turn off encryption before backup; however, I like to verify the backup file *.bak is in fact NOT encrypted before I send out to vendor.

Answer 1

Use "RESTORE HEADERONLY":

https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql?view=sql-server-ver15

Answer 2

Hi @CharlieLor ,

Full agree with Tom. Using below T-SQL. Please check the values of EncryptorThumbprint and EncryptorType. When the backup was not encrypted, the two values are NULL.

RESTORE HEADERONLY     
FROM DISK = N'C:\AdventureWorks-FullBackup.bak';    
GO  

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 3

Thank you for all the help, much appreciated!

Answer 4

Okay, just for kicks, I backup an encrypted database and tried this:

RESTORE HEADERONLY   
 FROM DISK = N'C:\AdventureWorks-FullBackup.bak';  
 GO

It's still showing me the EncryptorThumbprint and EncryptorType as NULL. That this means it didn't work regardless if it's encrypted or not, it's going to show NULL value for both columns.

Answer 5

Here's my actual test on a much smaller database size. As you can see both encrypted and un-encrypted *.bak files show the same NULL values for both of those columns. However, when I opened up in Notepad++, the Unencrypted shows some readable data while the Encrypted.bak file shows some goofy characters.