SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using a DR script with encrypted database backups

Using a DR script with encrypted database backups

Thom A
Thom A
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89319 Visits: 22712
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

SELECT @MediaPW = CONVERT(varchar, DecryptByKey(MediaPW))
WHERE DatabaseName = 'Customer';

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

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 :-)

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum