January 4, 2016 at 3:05 pm
Hello All,
I need help with restoring a TDE enabled database from Prod server to Dev server.
both source and destination servers are enabled with TDE already but has different certificates. I can't drop the certificate on Dev server as there are other databases enabled with TDE, not sure what options I have now to refresh Prod DB to DEV.
I kindly request to give any options if you have in your mind.
Thanks,
Shyam.
January 4, 2016 at 3:13 pm
firstly, read my articles at these links
http://www.sqlservercentral.com/articles/Security/122707/[/url]
http://www.sqlservercentral.com/articles/Transparent+Data+Encryption+(TDE)/91712/[/url]
gshyam23 92006 (1/4/2016)
Hello All,I need help with restoring a TDE enabled database from Prod server to Dev server.
both source and destination servers are enabled with TDE already but has different certificates. I can't drop the certificate on Dev server as there are other databases enabled with TDE, not sure what options I have now to refresh Prod DB to DEV.
I kindly request to give any options if you have in your mind.
Thanks,
Shyam.
Restore a backup of the cert from the prod server onto the dev server, you may then restore the TDE protected prod database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 4, 2016 at 3:33 pm
Hello Perry,
Thanks for your reply. However as I mentioned in my post, Dev server has certificate already and other databases on Dev are encrypted with TDE.
so I can't restore prod certificate on Dev server, also we don't want to restore prod certificate on to Dev as Developers has SA permissions on Dev and they can take backup of certificate from Dev and that can be used to restore prod backups which we dont want to give access to prod backup for developers.
January 5, 2016 at 1:14 am
If you don't want to create the Prod cert onto dev then your options, as far as I know, are:
Set up some form of integration to export / import the data from the Prod database to a Dev database.
or
Restore a Prod backup into a new database on the Prod server, remove encryption from this database and backup. Restore the unencrypted backup onto Dev and then encrypt the resulting database. Drop the unencrypted database on Prod and delete the backup file.
January 5, 2016 at 7:37 am
Copy the production certificate over and use this to restore the prod database on dev. Then re-encrypt the restored database with the dev certificate, and drop the prod certificate.
no one else will be able to recreaate the prod certificate onto dev if they do not have the passsword used to protect the production certificate
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply