Moving TDE database to a new database but having problems with the cert

Question

This is my first time working with TDE so hopefully I haven't done anything horribly wrong.

I'm trying to move my TDE database from one server to another. I have created a master key on the master database of the new server. I then restored my certificate from backup using the following command:

CREATE CERTIFICATE MyServerCert
FROM FILE = 'CertBackupFile'

When I created it originally I did not have a private encryption key so I just encrypted it with a password.

Now when I try to restore the backup of my TDE I'm getting the following error

Msg 15507, Level 16, State 1, Line 2
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

I've tried looking at the create statement but don't see where I can put in my original password. Also when I look at sys.certificates on my original server the pvt_key_encryption_type_desc = ENCRYPTED_BY_MASTER_KEY but on the new one it says NO_PRIVATE_KEY. So I assume I'm doing something wrong with the create.

Can anyone point me in the right direction to fix this?

Answer 1

You will need to create a master key on the new server - if it does not exit already.

USE MASTER;
GO
if not exists (select 1 from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password';

Then you have to restore your certificate:

CREATE CERTIFICATE serverCert  FROM FILE = 'C:\cert_Backups\servercert.cer'     
WITH PRIVATE KEY (FILE = 'C:\cert_Backups\servercert.key'
    ,DECRYPTION BY PASSWORD = 'strong_Passw0rD')
GO 

But for this to work you will have to make sure that you made a backup of the original certificate with the private key.

Backup certificate ServerCert 
 to file = 'C:\cert_Backups\servercert.cer'
 with private key (file = 'C:\cert_Backups\servercert.key',
 encryption By Password = 'strong_Passw0rD')
GO

If you only have a cer file and not a key you will need to backup your certificate again