Restoring a TDE Database to a Different Server

You setup TDE for your database. Great! Now you need to restore that database to a different server. Maybe you’re migrating off of an older server or maybe there is testing to be completed for an upcoming release. If we try to backup/restore or attach the database on a different server we’ll get some variation of an error stating “cannot find server certificate with thumbprint…” and the process will fail. There are a few steps to get the TDE database restored and we’re going to walk through them today.

Move Out

Our first step is to backup the certificate on the server where the database currently resides that was used when configuring TDE.

BACKUP CERTIFICATE SQLServerCert TO FILE = N'C:\SQL_TDE\SQLServerCert.cer'
WITH PRIVATE KEY (
		FILE = N'C:\SQL_TDE\SQLServerCert_key.pvk'
		,ENCRYPTION BY PASSWORD = 'S3cr3tP@$$'
		);

Next, we need to move these files to the destination server of our encrypted database. For the sake of this demo I’ll move them to a similarly named folder.

Is there already a master key on the destination server? If not, we’ll want to get that created which we can do with the following:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword';
GO

Then, we’ll want to restore the certificate from the source server:

USE master;
GO
CREATE CERTIFICATE SQLServerCert 
FROM FILE = N'C:\SQL_TDE\SQLServerCert.cer'
WITH PRIVATE KEY (
		FILE = N'C:\SQL_TDE\SQLServerCert_key.pvk'
		,DECRYPTION BY PASSWORD = 'S3cr3tP@$$'
		);
GO

Now, we should be able to restore the database without those annoying thumbprint errors.

Ensure Encryption

What is the state of our encryption on the destination server? What if we want to check encryption on other databases? We can check that with the following query:

SELECT DB_NAME(database_id) as 'Database', encryption_state, encryption_state_desc
FROM sys.dm_database_encryption_keys;
GO

From Microsoft’s article on sys.dm_database_encryption_keys, the possible encryption_state values are:

0 – No database encryption key present, no encryption

1 – Unencrypted

2 – Encryption in progress

3 – Encrypted

4 – Key change in progress

5 – Decryption in progress

6 – Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

There are various encryption_state values to represent encrypted, unencrypted, and everywhere in between.

Thanks for reading!