Back Up TDE Certificates and Private Key in SQL Server 2008
The most vital issue to consider when using TDE is that you need to back up and retain the certificate and private key associated with the encryption. If these things are lost or unavailable, you are not able to restore or attach the encrypted DB files on another server. The subsequent warning message displayed after creating a certificate drives home this point:
Warning: The certificate used for encrypting the DB encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you need to restore or attach the DB on another server, you need to have backups of both the certificate and the private key or you will not be able to open the DB. Backup up the certificate and private key.
Backing up the certificate, private key, and master key for the server is relatively direct. An example e of backing up the master key is shown in the subsequent SQL statement :
BACKUP MASTER KEY TO FILE = 'c:\sql2008backup\inasterkey1
ENCRYPTION BY PASSWORD = 1
somekeybackuppazzword$$1
If you can want to restore a DB backup on another server instance, a master key for the server need to exist. If one does not exist, you can create one by using the CREATE MASTER KEY ENCRYPTION syntax. After creating the master key, you are able to create the TDE certificate from a backup of the certificate from the original SQL Server, the subsequent example:
CREATE CERTIFICATE MyCertificate
FROM FILE = 'c:\sql2008backup\MyCertificate1
WITH PRIVATE KEY (FILE = 'c:\mssql2008\backup\MyCertificatePrivateKey1
,
DECRYPTION BY PASSWORD = 1
somecertbackuppazzword$$1
)
After the certificate is restored on the other server instance, you can restore the encrypted DB backup. At this point, the restore can be performed just as you would restore anyunencrypted DB backup. The restored DB is also encrypted and behaves like the original TDE DB.