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?

BACKUP MASTER KEY TO FILE = 'u:\MSSQL\DO_NOT_DELETE\Masterkey1'
ENCRYPTION BY PASSWORD = 'PW1234556'


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
LVL 1
jbla9028Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Steps:
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.

http://www.sqlsaturday.com/viewsession.aspx?sat=153&sessionid=11280
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
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!
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
To disable TDE you would execute

ALTER DATABASE dbname SET ENCRYPTION OFF

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

to make sure that the encryption_state is all completed.
0
jbla9028Author Commented:
Thank you all for the detailed answers!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.