After disabling TDE you still requires the certificate to restore the database

Published 29 June 09 11:46 PM | MartinBell

I came across this problem today when trying to restore a database that had previously had TDE enabled. (Note: the connect item seems to list the wrong database in the error message!!)

As you can imagine, after waiting 1 hour for the backup to restore I was not very happy!! But I did guess it was the fact that the database had been previously encrypted which lead me to the connect item. I then had to wait another hour to be proved correct!!

You don’t need two servers to witness this behaviour, the same thing will happen on a single server if you disable encryption on a database, drop the certificate and then backup and restore the database. To re-create this, see the following script.. The error message is the same as the connect item:

Processed 176 pages for database 'TDEDatabase', file 'TDEDatabase' on file 1.
Processed 1 pages for database 'TDEDatabase', file 'TDEDatabase_log' on file 1.
Msg 3283, Level 16, State 1, Line 1
The file "TDEDatabase_log" failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

From this error message alone it is not obvious that the issue is with a missing certificate, and you could spend a long of time investigating the problem and taking new backups if you thought it was either the backup file or database that was corrupt. So how did I ever know that this was the cause?

Always remember to backup any server certificate you use when creating database encryption keys and make sure you know which databases use which certificates on any given server!

As part of my investigations into SQL Server 2008’s new features, I had encrypted a database using TDE to demo what happens when you try to restore it to a SQLExpress instance. If you want to try this yourself see the following script. The error message you get is:

13 percent processed.
22 percent processed.
31 percent processed.
40 percent processed.
53 percent processed.
62 percent processed.
71 percent processed.
80 percent processed.
94 percent processed.
100 percent processed.
Processed 176 pages for database 'TDEDatabase', file 'TDEDatabase' on file 1.
Processed 3 pages for database 'TDEDatabase', file 'TDEDatabase_log' on file 1.
Msg 33117, Level 16, State 2, Line 1
Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'TDEDatabase'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 933, Level 21, State 1, Line 1
Database 'TDEDatabase' cannot be started because some of the database functionality is not available in the current edition of SQL Server.
 

It is only when the backup has processed all the file you get the error, but at least the error message is more informative!! The same error is produced by all the other editions of SQL Server that don't support TDE (i.e. every one except Enterprise and Developer!), even if TDE was removed.

 

You would really expect the backup header to hold information about TDE being enabled, but according to Books Online there does not seem to be any value/attribute for it. If there is, it's not used and I raised another connect item for this.

Comments

# SimonS Blog on SQL Server Stuff said on June 30, 2009 02:18 PM:

Martin is continuing to post some great new posts on his blog. I thought the last one is a real nasty

# Dew Drop – June 30, 2009 | Alvin Ashcraft's Morning Dew said on June 30, 2009 02:31 PM:

Pingback from  Dew Drop – June 30, 2009 | Alvin Ashcraft's Morning Dew

# Martin Bell UK SQL Server MVP said on May 31, 2010 11:54 AM:

I thought I would re-visit the problem of removing a certificate after disabling TDE

This Blog

SQL Blogs

Syndication