This article is only valid for SQL 2008 and SQL 2008 R2 as some feature were removed/improved in further versions.
When you restore an encrypted backup in another SQL Server, it either restored it fully encrypted with null values only, or doesn’t restore at all by raising the following error:
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xE11A199C1059C6F1E0223B56581CDCF3F043DFE8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
In order to successfully make the restore in a different server you will need to create a master certificate in the detonation and transfer the certificates and backups in that order.
First, identify all the objects affected. This includes the certificate, keys, and databases. With the query below you can list the encrypted databases. Notice the thumbprint is the same as the error raised originally. In this case we only have one certificate.
FROM sys.databases D
JOIN sys.dm_database_encryption_keys DEK
ON DEK.database_id = D.database_id
ORDER BY name
Second, identify the certificate by navigating in the source server to Master –> Security –> Certificates
Next, create a master key in the destination server. By default SQL creates one that is valid for system databases only. You need to create your own with the following syntax:
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='StrongPassword'
Command(s) completed successfully.
Next, you have to backup the certificate in the source and create a copy of it in the destination server. It must be with password and private key otherwise you will have the following error in the destination server “Msg 15507, Level 16, State 1, Line 1 A key required by this operation appears to be corrupted.”
BACKUP CERTIFICATE DB_Encrypt_Cert
TO FILE = 'Z:\Backup\DB_Encrypt_Cert.cer'
WITH PRIVATE KEY(
FILE = 'Z:\Backup\DB_Encrypt_Cert.prvk',
ENCRYPTION BY PASSWORD = 'StrongPassword'
Restore and create a certificate in the destination based on the backup file you took in the step before. Notice the name must remain the same, you will need the private key, password, and pay attention on the syntax it changed from Encryption to Decryption. The warning is OK, this is because the original certificate was not set with an expiration date.
CREATE CERTIFICATE DB_Encrypt_Cert
FROM FILE = 'E:\MSSQL\DB_Encrypt_Cert.cer'
WITH PRIVATE KEY(
FILE = 'E:\MSSQL\DB_Encrypt_Cert.prvk',
DECRYPTION BY PASSWORD = '7Hx81GbNaxHP65rsSfiKAaVvKvN5beUY'
Warning: The certificate you created is expired.
Finally, you can restore the database with your normal method and it will with no issues.