I want to say a few things about database backups that you need to know.
Wait a minute, haven’t you written about backups before?
Why, yes. Yes I have.
Aaand… you’re doing it again because?
Have you noticed the shocking number of questions that come up on SQL Server Central and #sqlhelp regarding backups? Have you noticed the incredibly huge number of people who don’t have backups at all? That’s why. To get the word out.
Oh, good point. Carry on.
Because backups are so easy, people tend to discount them. That is, until they need them. Then, suddenly, they become extremely important. Here’s a suggestion: Make databases important now. Learn how SQL Server backup works. Make sure you have backups on your systems. Make sure you have the appropriate backups on your systems. Spend time on backups prior to the emergency where you will suddenly wish you had spent more time on it. Tuning that query? It can wait. Adding a new piece of functionality? Right after the backups are set up. Please, give them the appropriate level of urgency that they deserve right now. I say this because later, when your server has crashed or someone has deleted the boss’s pay check, that level of urgency on that backup is going to shoot through the roof.
I mentioned it before, but it bears repeating, make sure you’re doing your backups correctly. Far, far, too many system administrators think that simply copying the files that define a database means that they have a backup. That’s just not true. Again, right now, take the time to learn how to backup SQL Server. Learn the differences between Full, Differential, Log, File/FileGroup and Snapshot. Learn how to automate these tasks so that your databases are protected. No, RAID is not a backup solution. I know you have a SAN, but it can still go wrong (ask me to tell you about the time our SAN admin switched it off by accident, that was exciting) and you’re going to need backups to recover.
Finally, and this is really the toughest part, you need to learn how to restore the database. That old saying, your data is only as good as your last backup, has a corollary from Kimberly Tripp (blog|twitter), your backup is only as good as your last restore. You actually need to try to restore your databases in order to know that your backups and your backup plan are good. You need to take the time, now, to test and practice restoring your database. There’s no other way to be sure about your backups.
Look, this is all repetition. I’ve said all this before. But unfortunately, for whatever reason, the word is not getting out, so, I feel the need to repeat myself. I’m trying to help you here. Backups are an urgent need. You should absolutely treat them that way.
For those interested in more detail on all this here are a few links:
Why Don’t People Run Backups
SQL Server 2005 Backups
Backup Verification
7 Preventable Backup Errors
SQL Server Backup and Restore for the Accidental DBA
Why is my log file full?
A SAN is not the correct answer, as you’ve said. Recently I’ve hit a case where an SSD was pulled out improperly from the fast cache by a technician. Can you say data corruption?
The only thing we could do on the two corrupted databases was restore from backup.
Ouch! Sounds like the SAN might not have been configured correctly, or there are fundamental architectural issues with it, but yeah, my point exactly. Thanks.
Because one can never have enough bad SAN examples, I’ve experienced two separate cases where a vendor did not mix drive batches in a SAN, which led to the inevitable result of a morning filled with cascading drive failures.
I’d also add a tip to verify that you’re putting them somewhere safe. When the roof springs a leak directly above the server room, you don’t want the taps sitting on the bottom shelf of a bakers rack next to the server…
Excellent stuff. Thank you. I too have had the leak in the server room problem.
[…] Database Backups: Things You Need To Do Now – Just in case you did not know already, Grant Fritchey (Blog|Twitter) will set you straight this week. There’s also some funny(so long as they don’t happen to you) DR stories in the comments. […]
I’ve seen a SAN controller failure completely toast a primary data file of a 600GB database and its only on-disk backup beyond recovery.
Goodbye database, there was no other backup and the damage was so bad that the repair routines just gave up (with faint sounds of electronic sobbing).
That was a top-end SAN, RAID 10 arrays, redundant everything (except copies of the database or backups)
Ow. That’s worse than anything I’ve seen.
When our SAN admin turned off our SAN, surprisingly enough, the majority of our databases were just fine. We had to run DBCC checks on everything and only had 4-6 out of several hundred that had issues. Ended up with only two point in time restores (our backups were on a seperate system). It was mainly a pain in the bottom & not the total nightmare you’re describing.
Thank you for raising awarness concerning DB backups – and just as important – testing the restore process.
We have a procedure in place for testing the backups but we always seem to run into a surprise or two.
Thanks again for the reminders!
turned off the SAN? i can do one better. few months back someone in the networking group had to pull out an old network switch. by accident he pulled out every single piece of fiber that connected servers to the SAN and brocade switches.
for db backups i have a daily job to import backupset tables into a central DB and use SSRS to run reports on the status of backups
I am also a database user and I take the backup of my database everyday. It keeps your database safe and secure.