SQL Security: Migrating/Restoring a TDE Encrypted Database to a Different Server.
April 30, 2019

Here are a few simple steps to follow when we are migrating/restoring a SQL Database that has TDE encryption enabled on the source serve/Database.

Step 1: We will need to back up the certificate and private key on the source server.

USE master;
GO
BACKUP CERTIFICATE TDECert
TO FILE = ‘C:\Backup\TDE_Certificate.cer’
WITH PRIVATE KEY
(FILE = ‘C:\Backup\TDE_Key.pvk’,
ENCRYPTION BY PASSWORD = ‘Tren#1000ton’)

Make the password complex and please note it down. We will need the password when we are restoring the certificate and private key
on the destination server.

Step 2: Copy the above certificate and private key to the destination server.
Example: to this location  C:\backups\  on the destination server.

Step 3: Create a Master Key on the destination server if it does not already have one.
The first order of the setup is to check to see if a Master Key and Certificate already exist within the Instance on the destination server.

This little code provides the names of master Key and certificates.
USE master
GO
SELECT * FROM sys.certificates.

If a Master key does not already exist, then we create one.
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘erTas#2647!!’
–Any password complex enough.
The above will create the Master Key.

Step 4: After a master key has been created, create a certificate by importing the certificate we created Step 1.
Here the ‘Decryption By Password’ parameter is same as that provided to export the certificate to a file.

CREATE CERTIFICATE TDECert2
FROM FILE = ‘C:\backup\TDE_certificate.cer’    
WITH PRIVATE KEY (FILE = ‘C:\backup\TDE_Key.pvk’,
DECRYPTION BY PASSWORD = ‘Tren#100ton’)

That’s it. You are now ready to restore the TDE encrypted SQL backup on the Destination server.

Prakash.