SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Backup Your Certificate for TDE

If you’ve enabled TDE, you need to be sure you have a copy of the certificate that protects the Database Encryption Key (DEK). If you follow my instructions, then you have one.

If you didn’t make a backup, or you have just discovered a TDE database, make one now, and secure the password you use with your DR materials (off site).

How do you make a backup? That’s easy. Use the BACKUP CERTIFICATE command. Here’s the command I use in demos:

USE master ; go BACKUP CERTIFICATE TDEPRimer_CertSecurity TO FILE = 'tdeprimer_cert' WITH PRIVATE KEY ( FILE = 'tdeprimer_cert.pvk', ENCRYPTION BY PASSWORD = 'AStr0ngB@ckUpP@ssw0rd4TDEcERT%') ; go


The certificate for TDE is in master, so you must make sure you’re in master for the backup. The TO FILE option lets you choose the file path. By default, this will be in the DATA folder for your instance, but you can choose other locations. You can give an extension if you like. This file is the certificate (public).

There is a private key portion of the certificate, which is backed up with the “WITH PRIVATE KEY” portion of the command. This is where you specify the password and provide the protection for your certificate.

You will need this password on restore, so keep track of it.

Filed under: Blog Tagged: administration, backup, encryption, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...