check if SQB is encrypted

chenchen300

Hi
I have just start to encrypt the SQB files.
But the old ones has no password, so i need some technique to check if need use WITH PASSWORD clause or not. 
Is there a way to check if SQB file is encrypted?

Eddie D

Hi, thank you for your forum post.

What you are seeking to achieve can be performed by running the sqbutility extended stored procedure. 

Open a new query window in SSMS connected to a SQL Instance running SQL Backup.

Run the following query:

USE master

GO

DECLARE @IsEncrypted INTEGER

EXECUTE master..sqbutility 1003, '<file_Location>', @IsEncrypted OUTPUT;

SELECT @IsEncrypted AS 'Is Backup Encrypted'

--modify / replace '<file_Location>' to the path for the backup file your wish to test, for example:
'D:\MyBackup\AdventureWorks.sqb'

If the IsEncrypted value is 0 - The backup file is a SQL Backup file, but contains no encryption.
If the IsEncrypted value is 1 - The backup file is a SQL Backup file, but contains no encryption.

You can perform the above in a right about way in the SQL Backup GUI.  Run the Restore Wizard -> Change the drop down from 'select from backup history' to 'Browse files for restore' ->Click Add files ->Select the backup files you need to check.  

In the background, the restore wizard performs a check to ensure that the backup file or files selected are valid, if the backup files pass the check and are not encrypted as confirmed by a 'tick'  on a green circle.  

If the files that require decrypting for the check to be performed (or the file is invalid) there will be a 'X' on a red circle and a message for you to enter the password to decrypt.

Many Thanks
Eddie

chenchen300

THANKS!!!!!!!!!!!!!!!!!