Disabling Encryption on TDE database causes restore error - by Grant Fritchey

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 423249 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 3/13/2009 7:13:32 AM
Access Restriction Public


If you create a certificate and enable encryption on a database and then later disable encryption on that database, backups taken from that database can not be restored to another server.
Sign in to post a comment.
Posted by Garyreeds on 2/3/2010 at 3:02 PM
Question to Microsoft:

Is Microsoft confirmed this issue as a BUG in SQL Server 2008 SP1? and

Is Microsoft declared that it's going to be fixed in next service pack release?

please clarify me

Posted by Microsoft on 9/8/2009 at 8:17 PM

I apologize for the delay. The backup may not be restored to another server as it may still be dependent upon a log backup that is encrypted. When TDE is disabled, the log file will stop being encrypted beginning with the next VLF. If any portion of the log back is encrypted, then the certificate will still be required. To avoid this, you can take a log backup and a database backup after TDE is disabled to remove dependency upon the encryption key (and hence the certificate) from that point forward. Alternatively, you can switch to the simple recovery model.

Posted by John Bell on 6/29/2009 at 8:19 AM
Having to install certificates when they are not needed conflicts with best practices.
Posted by Microsoft on 4/1/2009 at 10:47 AM

Thank you for your feedback. We will respond shortly.
Posted by Grant Fritchey on 3/13/2009 at 7:28 AM
No. Simply removing the certificate doesn't fix the issue.
Posted by Grant Fritchey on 3/13/2009 at 7:18 AM
I assume this relates:

But there was no resolution posted beyond the need to remove the key. Is that what's needed here too?