Encrypted passwords for restore

Betelgeuse

I have an Agent job that restores a backup of a production database to a development server every night. I would like to use an encrypted backup as the source, but I understand that I cannot use the encrypted form of the SQL Backup password for the restore.

Are there any clever ways to get around having the unencrypted form of the password visible in msdb?

mechie

Betelgeuse wrote:

I have an Agent job that restores a backup of a production database to a development server every night. I would like to use an encrypted backup as the source, but I understand that I cannot use the encrypted form of the SQL Backup password for the restore.

Are there any clever ways to get around having the unencrypted form of the password visible in msdb?

hi,

we have (soon had) the same problem. although it may not be an uber solution: we make a stored procedure with "with encryption" which does the actual restore.

so in your job comes a call to your own custom restore stored procedure and the stored procedure contains the actual statement with password (and is made with the WITH ENCRYPTION" keywords.

basically what i have works but it's not finished because it does not contain errorhandling and does not put errors properly back. due to my lack of experience it will take me some time to make it.

hopefully this provides enough info for you to make such a job.

eduard