asked
TDE encryption failed can't bring db online HELP!
Hello, I'm testing TDE encryption on my local DB, i've been through the process of creating key, certificate etc. However when I ran the command ENCRYPTION ON and I checked the percentage etc. it did NOTHING and ended up crashing my machine etc. I eventually managed to take the db offline, but can now not bring it online again. It says TITLE: Microsoft.SqlServer.Smo ------------------------------ Set offline failed for Database ''. ------------------------------ Cannot find server certificate with thumbprint '0x963E34B857F53F6595107AD1412C1A992A50504E'. Database '' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 33111) I cannot find the certificate that it is referencing anywhere, and as far as I know this db was never encrypted previously... any ideas??? I really don't want to lose this db as I have dev work on it! Any help would be MUCH appreciated!
What do you meant by "crashing your machine"? Do you remember what statements you executed including commands to take database offline? what does the SQL ERROR LOG states? I assume there is enough disk space available.
The bad news is, in general, if the certificate is gone, for whatever reason, there's little chance of recovering the database. Did you take a backup before you made the change? If so, go to the backup. If not, I'd suggest running backups prior to any kind of operation that might result in data loss. You can't be too paranoid as a DBA.
It might be the same issue which is fixed by Microsoft- "FIX: A database that has transparent data encryption enabled may be marked as "suspect" if the server certificate is dropped in SQL Server 2008 or in SQL Server 2008 R2" [
http://support.microsoft.com/kb/2463682][1] [1]:
http://support.microsoft.com/kb/2463682
Hi all thanks for your comments. I think the db basically got into an unrecoverable state. I have read that TDE can have issues with big tran logs... my dev copy of DB was in full recovery mode and had never done a backup (as it was under source control anyway) and didn't care about the data, so I may of had a large transaction log. In the end I had to ditch the db and rebuild it... so as Grant says always do a backup before doing this!
