That time I deleted a really important table

Brent Ozar recently linked to a previous article entitled, The First Time I Had to Restore a Database and as a completely original content writer I figured I'd share my story when I did exactly the same thing.

When I first started out working with SQL Server I was a sort of BI Developer, well sort of. We looked after a fairly critical database with a table called SuperImportantStuff or something.

One sunny day we received a request to remove some invalid data from said table, it was two rows that had entered the system incorrectly and had a specific column value that the delete statement could use without breaking anything. Well, not in the hands of a very inexperienced and trigger happy BI Developer anyway. The query was super simple, DELETE * FROM SuperImportantStuff WHERE SuperImportantColumn = 666 - or something like that and I checked the rows first before highlighting the statement and running the delete bit.

Only crucially I kind of missed the WHERE bit off, hit execute and up came the message telling me that 40 gazillion rows had been affected, well maybe not that many but it was definitely more than the expected 2 records. I did what every good database professional should do in this situation and panicked rather loudly before letting my manager know, who after a while conducting various checks concluded that I had indeed nuked the one of the most important tables for the entire business.

I should add that missing the WHERE clause features in Brents post too so I'm in good company.

The remainder of that day was spent learning the hard way how to restore databases from full and differential backups as well as performing point in time restores and perhaps the most important lesson (once or twice) of how to very carefully run delete statements. 

Once the database was fully restored for the thirtieth time I was informed that the database was used for reporting purposes and actually got re-populated twice a day via a SQL Agent job that completely wiped the tables before the data was reloaded. It was just too good an opportunity to teach me a lesson in database restores and to let me sweat on it for a while.

Without a doubt I understood how lucky I was, in two ways. It could have been a very different database or what if the database wasn't even backed up (surely that can't happen...right?!) but also I was very lucky to have a boss who took the time out of their day to use the opportunity to teach me something. I know it could have been very different story.