In asking various colleagues about the disasters or near disasters they have had with databases, I find to my astonishment that I am the only one who has ever, in a moment when my attention wandered, deleted a live database. It is odd, though, how products geared solely to mopping up from database disasters sell so well. It must be that these virtuous people are stocking up with the metaphorical mop-and-bucket as an insurance against the near impossible.

The worst event I ever suffered was due in part  to the wretchedness of the way that BLOBS (Text and images) were implemented in SQL Server. Whereas even poor PostgreSQL can handle strings of arbitrary length, SQL Server finds it awfully difficult. In fact, I have a theory that the guy originally tasked with implementing TEXT fields in SQL Server got more and more stressed until one day he looked up from his desk, shouted out ‘must join the penguins!’, flung of all his clothes, and disappeared forever. His colleagues were too distressed to clear up his work, which remains today unfinished inside SQL Server.  I once had a database implemented in SQL Server 6.5 that used Text fields. I could have made a mistake somewhere but some loose pointer sprayed angry bytes silently and stealthily all over my data. Unknowingly, I innocently did a neat and virtuous backup regime, until finally, the cancer had spread to the point that the database came up with a string of errors, failed the DBCC checks, and shut up shop. Vainly I went back through the backups. I had, for some time, unknowingly backed-up mangled data until I’d over-written the good ones, the 'Last-Known good backup' as the documentation describes it. The result was a great-deal of embarrassing downtime until I could retrieve the database.  It taught me a great deal about how SQL Server worked in the process, and possibly made me a more saintly person.

Another way I’ve seen production databases disappear is the build script. Some programmers like to work on the entire database script at once. This is fine in a development environment but, if you choose all the options in the build script, you are in some potential peril. I knew a DBA who was trying to fix a problem with the live database late at night, chose to work on a build script,  and who went to select a stored procedure and recompile it, but hit the ‘Execute Query’ button when there was no selection. It rebuilt the entire database from scratch but without the …er… data.

In the course of a busy day, I’ll be working on several databases at once. I wish one could colour-code the Query-analyser or attach different icons to different databases. I have been doing this so long that I always go through a little ritual before I hit that deadly ‘Execute Query’ button. It is so easy to think you are on the development database, decide to truncate a table and….

So I’d be really interested in hearing about how the great database disasters really happen, and how you managed to restore the data. Surely someone out there has experienced that ghastly clammy feeling of shock as one realises that one has just destroyed a live database system on which a corporation depends for its revenue, and for which even a short down-time is a disaster. No, sir I haven’t but I have a ..er.. friend…… And, of course, a prize for the best story.