Using a DR script with encrypted database backups

• Thom A

  SSC Guru

  Points: 99126

  More actions

  January 3, 2017 at 7:09 am

  #339171

  Hi All,

  I wanted to check how others had dealt with dealing with encrypted backups when doing a DR. I'll voice what I have a plan to do, but would appreciate any input that others might have.

  We won't be encrypting all of our back ups, some don't hold any personal or sensitive data, and would give other little to no information. Those we do, however, I plan to have their passwords stored in two places. One in our "Password Vault", which is a third party application. This is hosted on our file server, which is backed up to tape on a daily basis, which are collected and held off site for several days before being returned to be reused.

  Secondly, however, I want to store them in an encrypted field (using certificates and keys) in one of our databases. The reason for this is mainly the automation, as the third party application doesn't have any way to interact with it (that I know of), apart from the GUI.

  I already have a Powershell script which restores our backups. Firstly it restores Master (for obvious reasons), and then restarts the service and restores the rest of our databases. It works for both our SIMPLE and FULL backup process, and I and very happy with it's performance. It doesn't require any user interaction apart to prompts to run the Master Restore Script it has generated(for some reason it always failed when it ran it in the same task, so the user has to run that separately).

  My plan, therefore, is add change my existing to the following:

  -After the Master has Restored, the next database to be restored will be the one that holds the encrypted database passwords.

  -A prompt will be displayed where the database's own encryption password will need to be supplied.

  -Restore the User Database.

  -Amend the script the restore the remaining, however, add a variable into the script to obtain the password from the other database, and pass that. For exdample, the powershell would create a SQL script along the lines of:

  --Assume that I have already opened the Certificate

  DECLARE @MediaPW VARCHAR(100);

  SELECT @MediaPW = CONVERT(varchar, DecryptByKey(MediaPW))

  FROM DBA.sec.DBEncPW

  WHERE DatabaseName = 'Customer';

  RESTORE DATABASE Customer

  FROM DISK = 'C:\SQLBackups\Customer\Customer.Bak'

  WITH FILE = 1,

  NOUNLOAD,

  REPLACE,

  MEDIAPASSWORD = @MediaPW,

  RECOVERY;

  Would this work, or does anyone know of a better solution for a DR process?

  If people would like, I'm more than happy to share the Powershell script that I have created, in case it provides a bit more insight.

  Many thanks for any and all help 🙂

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk