Every time you do something major to the database, you need to create a backup. Repeat that three times. Then a few more times. Then do what I did and completely forget to follow the rules.
Our production environment consists of a master database that is replicated to multiple servers. There are non-replicated tables that we need to backup on a routine basis since the replicated data can easily be recovered in the event of a catastrophic failure but this other data would just disappear into the ether. Solution: move it to a new file group. Easy, right?
I created a nice little script with the help of SSMS to move all of the tables to a specially prepared file group. Somewhere in the process, I ended up getting sick. I completed my scripts and went home and left it to our migration person to move into production. It turns out that something failed in the script. Somehow temp tables that were supposed to contain the data were not present. All of the unreplicated tables were not only not in the correct file group, they were also completely empty.
So, what’s the solution here?
Mexico. Well, you pick up the phone, call your boss, and in the calmest voice possible you explain exactly what happened and own up to your mistake.
Why do you do this? Because it’s better to own up to a mistake and potentially get your head shoved into a toilet than it is to try to cover up your mistake. Covering up your mistake just makes you look shifty and untrustworthy. People don’t want to think of their DBA as shifty and untrustworthy. Eventually, everyone is going to remember that when your professional reputation and potentially your job (depending on severity) was on the line that you held your head up high and said ‘I completely forgot to take that backup and blew away our data with a bunk script.’ Hopefully what they’ll remember is the fact that you had the integrity to stand up and be honest, not that you completely nuked half the database.