I'm attempting to migrate several DB from an SQL 2014 to 2016 server. They all have TDE enabled, and are protected by a certificate.

I successfully copied the certificate, and I am able to manually export and import the database without issues. However when attempting to use the "Tasks > Copy Database" Wizard from the SQL Management Studio, I receive the error

"InnerException-->Cannot change database encryption state because no database encryption key is set."

Everything I can find on this says the error is a result of the encryption key/cert not being copied across properly, however I assume the fact I am able to manually export/import the DB means the cert is copied and working?

I believe the copy wizard will copy DB & server accounts without orphaning anything or requiring password changes, and the manual import/export will result in orphaned accounts and require password changes. As well as just the fact that using the Copy Wizard will be routinely simpler.

What am I missing?

  • Not really an answer to your question, but have you considered using the dbatools PowerShell module to do the migration? – HandyD Jan 28 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.