In this post I will show how the encryption actually works. I have backedup theAdventureworks2008R2 database on which I performed encryption.
Now I will login to another instance of SQLServer and will try to restore the database that I just backed up.
Now we get an error that SQLServer “Cannot find server certificate with thumbprint ‘0xE138B309A65069E6286242E8973F75D4ED3B7F11’.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 33111)”
So, in order to restore an encrypted database we need to first export the certificate we created on the instance on which the encrypted database backup was created and also a new master key for the new instance has to be created. The certificate is imported as shown below:
The certificate and key are now copied to the new instance server. The new master key needs to be created on the new instance and this is as shown below:
After the new master key is created we create a new certificate by importing from the certificate and key we just copied. This is as shown below:
Now if i try to restore the database it works without any errors as shown below:
Hope this helps!