Can a certificate be used to encrypt a backup?

AstoundingJam18

Using SQL Backup 10.1.0.1211

I couldn't find an answer in product documentation or other forums - (https://forum.red-gate.com/discussion/comment/150288#Comment_150288)

Question - I need to script out both backups and restores for automation but the password is in plain text. For the restore I even tried substituting the pw with the "OBFUSCATEDPASSWORD" value but it didn't work, seems to be doing a hash behind the scenes so only the actual pw will work. Is there a way to use a certificate or symmetric key instead of a plain text password?

petey2

It isn't possible to encrypt a backup using other means other than supplying a textual password.  One supported variation is to supply a file containing the password.  Users would set the backup such that only the SQL Backup Agent service startup user has access to that file.  Syntax is:

EXEC master..sqlbackup '-sql "BACKUP DATABASE ... WITH PASSWORD = [file:\\netshare\path\file.txt]"'

SQL Backup will use the first non-empty line of the file as the password.

AstoundingJam18

ok thanks. I appreciate the quick response. I'll test using a file as you suggested.