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