Yesterday, Jason Massie called me out to answer the SQL Quiz begun by Chris Shaw. The goal is to share a mistake you’ve made and what you’ve learned from it; hopefully, others will learn vicariously through you and avoid the same mistakes.
I didn’t have to think too hard about this one. I’ve made some small mistakes in my time as DBA, but only one really big one. You may have noticed my fondness for partitioning. My first partitioning project was to transition some very large existing tables to a new partitioning scheme. The plan was to stage the new partitioned schema and the newly partitioned data in the staging database, then perform a database rename. To make things easier on myself, the script I was using in DEV and TEST first dropped the tables and then rebuilt them. (I’m sure by now you know where this is going…)
One morning, while still in the midst of development, I sat down at my desk, opened up the script, and pressed F5. My first clue that I was in the wrong environment was the errors about dropping replicated tables.
Ultimately, the failure to drop replicated tables was what prevented this from being a total disaster. Nonetheless, several small look-up tables and one really large table (>1bil rows) were gone, all in the matter of 2 seconds.
What did I learn?
1) Never skip my morning coffee. Never.
2) I now always prefix my DEV and TEST scripts with something like this:
If @@ServerName Not In (‘DEVSERVER’, ‘TESTSERVER’)
RaisError(‘Sorry, this cannot be executed here!’, 15, 1);
/* Execute Code */
I’m calling on Jeff Belina, SQL Developer Extraordinaire. He doesn’t have a blog but that doesn’t mean he can’t post.
P.S. Thanks for the plug, Jason!