• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

TDE Encryption Database migration

I am not a DBA by anymeans so please bear with me.

I have a database that is encrypted with TDE. I do not know much about TDE but have been reading up on it. I need to export the database along with any necessary certificates required to move the database offsite to be recovered on another server. I get how to move the database using a backup. My question is how do I export the certificates properly. I read ac ouple articles and I'm not clear if its 1 certificate per server or 1 certificate per database, both, or "it depends." I successfully ran this query and exported the master key from a blog I was reading. I just need to know if there are any other certificates or problems I may run into recovering the database on the new server?


this query executed and successfully exported a file which I now have backed up.

Do I need to export any other certificates?

SQL server 2008 Enterprise
  • 2
  • 2
2 Solutions
DBAduck - Ben MillerPrincipal ConsultantCommented:
the most important piece is the certificate.  you can recreate a Master Key in the master after the fact, as well as a database encryption key.

the components are as follows:

Master Key in the master database
Certificate in the master database
Database Encryption key in the Encrypted database, but that key goes with the database when you back it up.

Backup database
Backup Certificate

New server
CREATE Master Key
Restore Certificate from backup (restore in the master database)

Once these are in place you can restore the database to that server.

here is a link to the Presentation and scripts I have given at many different SQL Saturdays. In the script you will see the process of backing up the Certificate and recreating it on another server to restore the backup.  Just click the download button and you will get the scripts and presentation file.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
In addition to that excellent answer.
It's important to take the certificate and put it in a safe (preferably on 2 separate media).
Otherwise you wont be able to use your backups in a DR scenario.
Otherwise you have the classic: a backup solution, not a restore solution.  :-)

Regards Marten
jbla9028Author Commented:
Thank you. Also I see that you have mentioned that there's ways to disable TDE. If I disable TDE, can I make an unencrypted backup without causing any issues with the database's functionality? If so what query output do I need to complete to disable TDE? Thanks!
DBAduck - Ben MillerPrincipal ConsultantCommented:
To disable TDE you would execute


Make sure that you look at the
select *
from sys.dm_database_encryption_keys

to make sure that the encryption_state is all completed.
jbla9028Author Commented:
Thank you all for the detailed answers!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now