USE master;
 GO
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
go
 CREATE CERTIFICATE DB_TDE_Cert WITH SUBJECT = 'DBTDE_Cert'; 

 

 --Backup the key

USE master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd';
BACKUP MASTER KEY TO FILE = 'C:\data\EncryptionDemo\DBTDE_Cert.key' ENCRYPTION BY PASSWORD ='P@ssw0rd';

GO

BACKUP CERTIFICATE DB_TDE_Cert 
 TO FILE = 'C:\data\EncryptionDemo\DBTDE_Cert.bak'
 WITH PRIVATE KEY ( FILE = 'C:\data\EncryptionDemo\DBTDE_CertKey.bak' ,
 ENCRYPTION BY PASSWORD = 'P@ssw0rd');


USE HKDB ;
 GO
 CREATE DATABASE ENCRYPTION KEY
 WITH ALGORITHM = AES_128
 ENCRYPTION BY SERVER CERTIFICATE DB_TDE_Cert;
 GO
 ALTER DATABASE HKDB
 SET ENCRYPTION ON;

 GO


 
 

Move a TDE Protected Database to Another SQL Server

USE master;
 GO
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
go
 

CREATE CERTIFICATE DB_TDE_Cert 
FROM FILE = 'C:\data\EncryptionDemo\DBTDE_Cert.bak'
WITH PRIVATE KEY 
(
    FILE = 'C:\data\EncryptionDemo\DBTDE_CertKey.bak',
    DECRYPTION BY PASSWORD = 'P@ssw0rd'
);

--RESTORE THE TDE ENABLED DATABASE BACKUP