Q&A with Grant Fritchey: How to Back up Your Database
A key responsibility of database administrators, or DBAs, is to ensure they're adequately backing up their databases. This can be overwhelming to some DBAs, who often stepped into those roles by chance. That's what happened to Grant Fritchey, a product evangelist at Red Gate Software Ltd and upcoming sessions speaker at this year's Live! 360 event. Grant takes some time to answer some of my questions on his backup experiences.
Q: What is an accidental DBA?
A: I'm an accidental DBA. Lots of people are accidental DBAs. The accidental DBA is the poor schmo who was standing in front of the boss when it was decided that someone needed to manage this SQL Server thing that someone had just purchased. It's the IT guy or developer who has no choice but take on the duties of a DBA despite a lack of training. It's probably the most common path to becoming a DBA. Because it's the path I took, I have a strong sympathy for accidental DBAs, so I always try to help them out when I can.
Q: Are you saying there are quick and dirty ways to back up databases?
A: I wish. No, there are really no shortcuts. There are very particular ways that a database has to be backed up, and they really are different from other backups.
Q: Why is database backup different from, well, just backup? Or is it?
A: No. While SQL Server does create files on disks on the computer -- and you'd think you could just copy those files -- there are several problems with that approach. First, SQL Server keeps the files open the entire time, so you just fail to read the file. But that's easily gotten around, which leads you to the bigger problem. Presumably, someone is accessing your data all the time. There's probably also someone creating or modifying your data pretty constantly, too. These transactions are stored in a very particular way to the files by SQL Server in order for SQL Server to protect the data and make it correct. Copying a file, well, that doesn't take this process of transactions into account. So you need a method for SQL Server backups to get a copy of the database that both protects the data and is consistent with the transactions.
If heading out to Orlando for this year's Live! 360 event in December, make sure to catch Grant's workshop, "Database Backups for the Accidental DBA."
Posted by Doug Barney on 11/07/2012 at 1:19 PM