Do you have a "miscellaneous" script that contains numerous disparate t-sql statements that you usually run on an ad-hoc mode? If yes then do yourself (and others that depend on you) a favor: select the whole script and click on the "comment out" icon. That way, if you ever happen to accidentally "execute" without first selecting the statement(s) that you really wish to execute nothing bad will happen. Otherwise, in just seconds you may end up creating a huge mess.
What prompted this? It was the desperate face of a very good database programmer who had just "successfully" executed one of those miscellaneous scripts and completely wiped out a couple of production tables, deleted certain rows from other tables and updated a few more. He did not lose his job but those few seconds of “terror” have probably shortened his life expectancy by a few months. It would have never happened if his miscellaneous statements were all commented out.
Are there other lessons one can learn from this story? Yes, plenty of them – there is a lot that the DBA can do to prevent such things from ever happening (just do a search for sql server best practices and you will find a lot of great advice) but the goal of this was to remind you of a very simple measure that everyone who “touches” the database can easily take.
Here is the product plug-in: this was a great showcase for our xSQL Data Compare. Here is what happened next. They mounted the previous night’s backup on a verification instance and applied the logs up to the last log before the "unfortunate event". Next they used xSQL Data Compare to determine what was different and generated a selective synchronization script that basically reversed all the changes that the un-intentional execution of that "miscellaneous" script had caused. Thanks to a reliable backup scheme and to xSQL Data Compare of course, the damage was contained to a couple of hours of work with no data loss but it could have been a real disaster.
Please feel free to leave your comments here.
Subscribe to:
Post Comments (Atom)
1 comment:
Back when I worked with T-SQL, I used to store "utility SQL" as stored procedures, and execute them as needed. No risk of screwing up with half a dozen queries then - and no risk of losing the damned file either...
Post a Comment