Sql-server – Restore fails on unencrypted database

encryptionsql servertransparent-data-encryption

I tried to restore an unencrypted database to a different server and it failed with the error:

"Cannot find certificate with thumbprint".

This suggests the database is encrypted but it is not. There are other databases on this server that are encrypted, but not this one.

When I query the sys.dm_database_encryption_keys table the encryption_state for this database is 1 = Unencrypted.

I see other databases where the encryption_state value is NULL = Not Encrypted.

So I have 2 questions:

  1. What the difference is between the values NULL and 1?
  2. Why am I getting this error on an Unencrypted database?

Thank you for your time considering my question.

Best Answer

The error is potentially caused by this database being encrypted previously and had encryption disabled. If portions of the log contain encrypted data, the backup will also have this encrypted data and hence need the certificate.

Try clearing the log (backup the transaction log) then taking a full backup again and you should be able to restore the new backup.