Monday, March 19, 2012

Backup database with encrypted column data

Hi,
I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.

How could I backup/restore master key, symmetric and asymmetric key created for my database?

Thank you

Hi,

This might help

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLEncryption.doc

Regards

|||Hi,
Thank you for reply.
I didn't see any BACKUP command mentioned in doc for asymmetric/symmeric keys. Do I need to backup sys.asymmetric_keys and sys.symmetric_keys table manually?

Thank you
|||

You don't need to backup or restor any key, to be able to use your database backup on another machine. You just need to know the password protecting the database master key. On the machine on which you restore the database, you will have to reencrypt this master key with the service master key of the database server. You can do this with the following statements:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Thanks
Laurentiu

|||Refer to books online that links up a section about BACKUP and RESTORING encryption keys http://msdn2.microsoft.com/en-US/library/ms157275.aspx link.|||

I think the keys in the new db you restored must rebuild,because the service master key maybe different.

you can't restore a db by the command simply.

No comments:

Post a Comment