Eduardo Olivera (1/28/2013)
We will soon be converting our existing production databases to a a system where we use certificates on the actual data holding databases and procedures on a sp only database to limit access to the data by the application. We regularly obfuscate and backup production data to restore it in QA and Dev where we need to preserve the same arrangement. I would appreciate any assistance on how to perform this whether I need to drop it and recreate it on the destination server or if it is possible to move certificates across servers.Thanks in advance,
Eduardo
You don't need to recreate the certificates, but what about security? This is not a good idea to use the same certificates and keys on prod and dev.
After restore the backup on a new instance, you just alter DMK. And if you protect DMK only by password, you don't need to do anything.
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'pass';