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
7 Preventable Backup Errors
SQL Server Backup and Restore for the Accidental DBA
Why is my log file full?