You want to restore an encrypted SQL database but ended up with below error message.
Restore of database 'SID' failed.
Cannot find server certificate with thumbprint 'abc123'
Solution
Run the following query to capture the certificate id. There will be multiple certificates displayed, but you can always differentiate, or compare with the thumbprint.
In Source SQL Server
select * from sys.certificates
Once you have capture all the necessary information above, run the following query,
USE [master]
GO
BACKUP CERTIFICATE THE-CERT-NAME-ID
TO FILE = 'A:\File\FILE-CERTIFICATE.cer'
WITH PRIVATE KEY ( FILE = 'A:\File\FILE-PRIVATE-KEY.prvk',
ENCRYPTION BY PASSWORD = 'YOUR-PASSWORD')
##### INFORMATION
## THE-CERT-NAME-ID = The certificate id
## FILE-CERTIFICATE = The certificate file name. Ensure the path is valid
## FILE-PRIVATE-KEY = The private key. Ensure the path is valid
## YOUR-PASSWORD = Maintain the new strong password
So, once the above activity is done, copy your certificate and private key to the target server. Make sure to put the cert, private key files together with the database backup files.
In Target SQL Server
Run the following command,
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YOUR-PASSWORD';
GO
CREATE CERTIFICATE THE-CERT-NAME-ID
FROM FILE = 'B:\File\FILE-CERTIFICATE.cer'
WITH PRIVATE KEY (FILE = 'B:\File\FILE-PRIVATE-KEY.prvk',
DECRYPTION BY PASSWORD = 'YOUR-PASSWORD')
Next, restore the SQL database again!
