SQL Backup; Files are encryped, GUI is not set that way

SirRandall

Forgive me if this is a user-error, as I inherited several AG instances being backed up by Red-Gate SQL Backup 10 and I'm still getting comfortable with the product.

In the SQL Backup GUI, I've reviewed each of the Full backup jobs that are scheduled for each of the 3 AG nodes. Each node has a FULL backup job scheduled, but with the AG configuration only one of the backup jobs is actually doing the work....as CHECK_PREFERRED_AG_REPLICA and SECONDARY_REPLICA_COPY_ONLY are both defined in all three jobs. This appears to be operating as expected.

However, in the SQL Backup GUI, and in the script created and inserted into the SQL Agent job, there is no selection for Encryption and/or password. I've confirmed that Encryption is NOT selected in the GUI.. I've confirmed it multiple times.


However, when I recently tried to perform a restore to a different server, I was prompted for the encryption password.

I'm trying to figure out how these backup files are getting encrypted, if the backup job doesn't call for it.

Example of script from Node A:
 EXECUTE master..sqlbackup '
  -SQL "BACKUP DATABASES [dbname1,dbname2,dbname3]
  TO DISK = ''\\192.168.1.211\MyBackups\FolderName\<DATABASE>\FULL_COPY_ONLY\<AUTO>.sqb''
  WITH ERASEFILES_PRIMARY = 14
  , MAILTO_ONERRORONLY = 'someone@red-gate.com'
  , CHECKSUM
  , DISKRETRYINTERVAL = 30
  , DISKRETRYCOUNT = 10
  , COMPRESSION = 4
  , THREADCOUNT = 15
  , VERIFY
  , CHECK_PREFERRED_AG_REPLICA
  , SECONDARY_REPLICA_COPY_ONLY"'

I'm sure I'm missing something obvious, but what?

Thanks in advance!

petey2

Given that script you sent, the backups are not being encrypted in any way.  

If you run this command from Management Studio:

EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = [<your backup file>]"'

for any of the backup files that were created using the above script, does it prompt you to enter a password?

SirRandall

Thanks for the response.
I did run the script you provided, and the results indicate that I used the wrong password.

SQL Backup v10.0.12.1149
------------------------------------------------------------------------------
Reading SQB file header of "D:\Temp\filename_here.sqb"
 
Wrong password entered.
 
SQL Backup exit code: 710


I must be missing something somewhere.

petey2

Could you please post the result of this query:

EXEC master..sqbdata 'SELECT * FROM backuphistory a INNER JOIN backupfiles b ON a.ID = b.backup_id WHERE name = ''<your backup file name>'''

SirRandall

When I run this query against the instance where I attempted the restore (that failed), I get zero results.
Did you intend to have me run this against the instance that created the backup?

petey2

Yes, please run the query on the instance that created the backup.

SirRandall

Sorry. Had trouble finding it. Then I recalled that it's set up as FULL COPY ONLY, so it's not in the log.

petey2

SQL Backup logs all backup processes, regardless of the settings used.  

If you can't locate the record, would you be able to locate the SQL Backup log file for the backup process that created the backup file?  You can use the SQL Backup GUI on the instance that created the backup to retrieve the log file for that specific backup, or you could look in the folders where the logs are created (default folder is C:\ProgramData\Red Gate\SQL Backup\Log\<instance name>)

SirRandall

Thanks Petey2. It's late. I'll get back to you Monday. Thanks for the help.

SirRandall

This is still a mystery. Perhaps I missed something in the original config.
Regardless, using the GUI I have revised all of my backups to be encrypted and it appears to be working as I expected.
Chalk this one up to user error I guess.