Use "RESTORE HEADERONLY":
How to verify if a backup "*.bak" is encrypted?
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.
9 answers
Sort by: Most helpful
-
-
CathyJi-MSFT 21,111 Reputation points Microsoft Vendor
2021-08-19T01:25:10.53+00:00 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.
-
CharlieLor 551 Reputation points
2021-08-19T13:13:13.337+00:00 Thank you for all the help, much appreciated!
-
CharlieLor 551 Reputation points
2021-08-26T14:59:30.967+00:00 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.
-
CharlieLor 551 Reputation points
2021-08-26T16:27:56.14+00:00 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.