A guide to restore a TDE database backup to another SQL Server Instance

Question: I have a SQL Server with TDE enabled and the user databases are TDE configured. I need to take a backup and restore the TDE enabled database to another SQL Server Instance . Could you take me through the steps  including prerequisites?

Answer:      To successfully restore a TDE enabled database onto a target SQL Server , the target SQL Server will need to be : TDE enabled & have a copy of the certificate used by the Source Server.  Following is a progression of how to migrate the TDE enabled database , and will vary according to your current set up 

On the Source Server - Check to see if the current database is encrypted 

SELECT 
d.name,
d.is_encrypted,
dek.encryption_state,
dek.percent_complete,
dek.key_algorithm,
dek.key_length
FROM
sys.databases as d
INNER JOIN sys.dm_database_encryption_keys AS dek
ON d.database_id = dek.database_id

Take a backup of the TDE enabled database 

backup database test to disk = 'G:\test.bak'

Move the .bak file to the other SQL Server Instance - which is TDE enabled .  If you haven't yet TDE enabled the target server use this link to Database Master Key Creation -A guide to setup TDE for SQL Server

If the target server is already TDE enabled including the Database Master Key creation - then you won't need to create a new Database Master key. 

On the target SQL Server - Execute a RESTORE FILELISTONLY to view the files within the database 

restore filelistonly from disk='G:\test.bak'

This message may appear - if there isn't a valid certificate on the target server 

Msg 33111, Level 16, State 3, Line 5
Cannot find server certificate with thumbprint 'XXXXXXXXXXXXXXXXXXXXXXXXXXX'.
Msg 3013, Level 16, State 1, Line 5
RESTORE FILELIST is terminating abnormally.

To overcome this error message the certificate and the private key created on the source server using the BACKUP CERTIFICATE statement should be copied to the target server. When copied over - use the CREATE CERTIFICATE for addition to the target server master database /

This is an example of the CREATE CERTIFICATE executed on the target server. In this example - the .cert and .pvk files are obtained from a BACKUP CERTIFICATE statement executed on the source server. I have then copied them across to the target server , to accessible local drive , and executed the CREATE CERTIFICATE statement 

USE MASTER
GO
CREATE CERTIFICATE TDETest_Certificate 
FROM FILE = 'G:\tde_certs\TDE_Cert.cert'
WITH PRIVATE KEY (FILE = 'G:\tde_certs\TDE_CertKey.pvk',DECRYPTION BY PASSWORD = 'MyverystrongPWD' );

The certificate and the private key that we created from the source server with the BACKUP CERTIFICATE statement have to be copied to the destination server instance. Once the files have been copied, use the CREATE CERTIFICATE statement to add the certificate to the master database in the destination server. The FROM FILE and FILE parameters point to the certificate and private key files.

As an added note - some thought should be given to managing the certificate, private keys and passwords. For example in a situation of losing the server and not being able to recover the server and  database objects , you may need to restore a TDE enabled database onto a new server . 

If you see this error message , speak to the Administrator who is managing the passwords used by the TDE process. 

Msg 15465, Level 16, State 6, Line 20
The private key password is invalid.

If the CREATE CERTIFICATE works successfully you should be able to view the certificate in the sys.certificates

SELECT name AS certificate_name,
pvt_key_encryption_type_desc AS pvt_key_encryption,
thumbprint
FROM master.sys.certificates

The certificate is now in place to support the restore of the  database onto the target SQL Server.

USE [master]
RESTORE DATABASE [test] FROM  DISK = N'G:\mytdeenableddb.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

Read more on TDE

Impact on TDE encryption in progress on SQL Server restart

Tested TDE with Instant File Initialization on SQL Server databases