Can a certificate be used to encrypt a backup?

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?

Best Answer

  • petey2petey2 Posts: 55 Silver 2
    Accepted Answer
    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.
    SQL Backup - beyond compression

Answers

Sign In or Register to comment.