Friday, April 20, 2012

Data Safety Is No Accident

(With apologies to David Foster Wallace.)
I follow various tags on stackexchange sites, primarily database-related things. If you do this for long enough, you will see certain questions come up again and again. Frequently Asked Questions. FAQs. This is not a new phenomenon, it dates from the days of USENET and probably even before then
One of those FAQs is: "OMG, something horrible happened to my database, I don't have any backups and now I think my boss will yell at me and hurt my feelings. How can I restore the data before that happens?".
As a DBA, your first job is being able to restore the data. Just backing up the database isn't any good if you can't restore it. It doesn't matter if you are using SQL Server, Oracle, MySQL, postgresql or any other data storage technology. It doesn't matter if you are an "accidental DBA" and would rather be coding. (I started my career as a coder and, frankly, I would rather be coding too.) People rely on you to protect their data in ways that they don't understand and can't do for themselves.

Backups should be boring. Restores should only be slightly more exciting, since they should be rarer (much rarer) than backups. If your backups and restores are not boring, you are doing them wrong.

Before you run upgrade or update scripts from the development team, protect yourself. Stuff happens even when people are doing their level best. A last minute change might have deleted a crucial term in the WHERE clause of that update statement. The script that you recieve might be a concatenation of several other scripts, and the whole Magoo has never been tested at once. Some snafu with the VCS means that you are given the wrong version of the file.

Ask yourself, "If something goes bad with this script, what will I do to reverse it's effects?" As a DBA, can easily protect yourself by taking an extra backup (remember to use Copy-Only backup commands if you are using a differential backup strategy) or by taking a database snapshot.

For scripts that run on large databases that only change a procedure or view, it might not be worth the time to back up the whole database. I like to script out the body of that object. I was doing that so often, I wrote a little PowerShell utility so I could do it quickly. Sure, that procedure is probably in the VCS, but sometimes finding the right version in a hurry isn't so easy.

If you don't have DBA rights, you can protect yourself by adding some transactional protection to the script that you have been handed (be sensible - you don't want excessive log file growth). If you want to make a backup copy of the table you are modifying, that is OK but don't let it hang around forever. Add an item to your calendar that will force you to come back and drop that backup copy. Or consider BCP-ing the data out. It's quick, once you get the hang of the bcp command syntax.

If you are inserting rows and find a problem, you might be able to delete the new rows if you can describe them with a WHERE clause. Many tables have some sort of createdby or updatedby columns. BUT, watch out for triggers, as they can modify data in other tables in surprising ways and, when it comes to databases, surprises are almost always bad.
Post a Comment