So it happened....
I screwed up....
Here's the setup...Wednesday morning, a user deletes about 1600 rows out of a table (why they have that type of access is beyond me and probably will be good for another blog entry). NO PROBLEM! Let me log into the server, and locate my backup from last night. Click, double click, another double click, scroll...scroll...scroll some more?...panic sets in. Where in the H-E-Double Hockey sticks is my backup from last night (It came out a lot worse than that), where is my backup from Tuesday night, Monday, SUNDAY!
As you can guess my Rookie badge was gleaming at this point....
What had happened was (keep in mind SQL vets...Rookie here) my drive ran out of disk space, so the lovely Maintenance Plan (Geez I hate Maintenance Plans) simply stopped working, I guess it really had no other choice. And of course their is an alert (setup by our AD guys) that sends me an email letting me know when a drive is low on space, but I'm not alerted until the drive falls below 100mb, does me a lot of good on a backup drive. The big kicker was I have alerts sent out every night letting me know the status of my Maintenance Plans, and I religiously check them (honestly I do). But this time, no not this time, Not starting on Monday, I didn't or Tuesday, and obviously I didn't on Wednesday. The name is DUMASS!
So lets get back to the issue...
So I am telling the applications admin the bad news...and then BAM! WAIT! Hey, app guy, do we know which table was deleted? Can we find out? And while you're at it, get an estimate when the table was last updated. Turns out they did know which table was "edited" and no changes have been made in about a week! (Prior to Wednesday of course) And it turns out Brent (thats me) has a freaking wonderful tool from RedGate called SQL Data Compare. So I simply compared my Saturday night backup with the current state of the database, saw the table that was nulled out, all 1600 rows, and synchronized only that table with the current database. And like freaking magic, the table had been restored.
Now obviously this was a hail mary, almost exactly like Flutie's Miracle in Miami, "almost" exactly. This was without a doubt a lessons learned, in my young SQL career.
So what did I do to assure this will not happen again, and avoid being fired in the future?
1. Went straight to my AD guy and had him adjust the alert, so alerting occurs in the Gigs, and not the Mbs.
2. I tuned up SQL Monitor, so I get up to the minute reporting.
3. Then I said screw the local disk for storing backups for seven days, I'm going to utilize Data Domain
3a. Write a script that sends the backup off the disk, and up to Data Domain, and make sure the script cleans up after itself, deleting the backup locally.
4. And of course, check my ALERTS!
And there you have it, my horrible Wednesday, but fortunately, I learn from my mistakes, unfortunately, thats how I learn!
If anyone out there has suggestions, please feel free to comment and help a SQL Rook out!
No comments:
Post a Comment