Many of you have seen some sample codes from Books online tht shows u how u can implement TDE. wht books online does not show u is (at least i cud not find an example), as to how to restore a DB tht has been encrypted with TDE on another instance. here is an example..
– step 1, on instance 1, we first set up the keys and certificates and then enable TDE and then take a manual backup (code for backup and restore is not given, u can do it from GUI)
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pa$$w0rd’; go CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate’ go USE AdventureWorks GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert GO ALTER DATABASE AdventureWorks SET ENCRYPTION ON GO
if we take a backup if the above database, we will not be able to restore it on another instance becuase TDE is enabled and the DB is encrypted. We can only restore it if we have the same key and certificate on the 2nd instance tht we used to encrypt the above DB on the 1st instance.
–step 2, on instance 1, backup the certificate along with the private key
BACKUP CERTIFICATE MyServerCert TO FILE = ‘c:\MS.cer’ WITH PRIVATE KEY ( FILE = ‘c:\MS.pvk’ , ENCRYPTION BY PASSWORD = ‘amit’ ); GO
– step 3, on instance 2, create the master key if it is not already created and then restore the certificate with the private key. after this resotre the DB and it will be successful
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pa$$w0rd’; CREATE CERTIFICATE MyServerCert FROM FILE = ‘c:\MS.cer’ WITH PRIVATE KEY (FILE = ‘c:\MS.pvk’, DECRYPTION BY PASSWORD = ‘amit’); GO
Hope this helps.