This is a guest editorial from Brad McGeHee
In the past, I have written and spoken a lot about SQL Server "best practices". One that I emphasize over and over again is the need for DBAs to verify, regularly, the integrity of their database backups. However, I sometimes feel my advice is falling on deaf ears. When giving a presentation, I often ask how many DBAs in the audience regularly verify their backups. Invariably, few hands are raised. When I visit a SQL Server shop to review their database maintenance plans, I always ask the same question. Very few even know what I am talking about.
Why is this? Do some DBAs have a false belief that database backups are always perfect? Perhaps these DBAs don't know the mechanics of to how to verify a backup? Maybe, they just don’t have the time to perform the verification? Most likely, they haven't even given the issue any thought because they have not – yet – had a restore fail, and had to explain to an irate manager why their data is lost.
Backup verification is a vital part of a well-designed database maintenance plan. Fortunately, I learned this lesson very early in my career. Back in 1982, a time before hard disks were available for desktop computers, I worked for a computer retail store that performed its accounting on a desktop computer, storing the data on floppy disks. Every day, the owner of the company would religiously make backups of the floppy disks and then sit these backups on the floor, next to her desk. Also, every night, she would vacuum her office, keeping it nice and tidy to meet with customers. One day, one of the main floppy disks became corrupted, so she went to her backup floppy disks in order to restore her data. The only problem was that backup floppy disks had all been corrupted by the vacuum cleaner’s strong magnetic field. None of her backups were good. She had to start from scratch, reentering all of the lost data by hand.
It was a very painful lesson and ever since then I have been a fanatic when it comes to making lots of backups, and regularly verifying that I can restore them if needed.
I'd be interested to hear how regularly people here test the integrity of your database backups, how often you've encountered environments where test restores were never performed, and what excuses were given for this.
Brad M McGehee
Director of DBA Education
Red Gate Software
The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
or now on iTunes!
- Windows Media Podcast - 19.8MB WMV
- iPod Video Podcast - 18.7MB MP4
- MP3 Audio Podcast - 3.8MB
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.
