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!
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.
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!