Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Paul S. Randal Posts Some Sobering Statistics

Paul S. Randal Posts Some Sobering Statistics

  • Comments 6
  • Likes

Paul’s name should be familiar to denizens of the SQL Server blogosphere.  Along with his wife, Kimberley Tripp, he’s a principal of SQL Skills (home also to the redoubtable Bob Beauchemin, with whom I share a birth date of significance to SQL Server aficionados); given that his history includes authorship of DBCC in SQL Server 2000 and major components of DBCC in SQL Server 2005, the name of his blog is among the great puns out there: In Recovery... | Paul S. Randal on SQL Server.

Paul’s a great guy, and if you haven’t caught one of the presentations he and Kim give, you’re definitely missing out.  These folks are two of the finest SQL Server presenters in the world.  No wonder they’re found at the center of the MCA and MCM Database programs.

Paul’s blog is always a technical cornucopia; recently, he got the inspiration to augment his technical posts with surveys of his community (brilliant, Paul!  I wish I’d thought of that!).  His first one is a doozy: How often do you validate your backups?

  • Almost twenty-five percent of respondents never validate their backups!  Huh?  Paul wisely quotes Kim, and I’d like to reinforce her statement here: you don’t have a backup until you’ve restored it.
  • Forty-one percent of respondents regularly or occasionally validate their full backups (but not differentials or logs!).
  • Twenty-eight percent of respondents regularly or occasionally validate their full backups, their differentials, and their logs.

If these statistics hold across the general user base, many of you may be working without a net.  There’s obviously lots of work we can do here, and as Paul wisely points out in his article (which you really should read in its entirety), your high availability/disaster recovery plans should never rely solely on backups.

Paul’s second survey pertains to index maintenance.  You can participate here.

Thanks, Paul, for the thought-provoking statistics and sage counsel.

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Comments
  • Validating your backups is great, but IMHO it's not enough.  You need to actually do a test restore from time to time (once a month would be good, once a week better ... one customer I talked to does a test restore for EVERY backup they take and while I think that's slightly overkill they can certainly sleep easy at night knowing with almost 100% certainty that their backups will restore in the case of a disaster).

  • Your post title is, as Dilbert's pal Wally might say, "Well played", a fine foil to Paul's blog.

    This is absolutely amazing information.  Thanks for spreading the word, Ward.  How can we, as trusted advisors to our customers, allow this to happen?  I feel the urge to blog about this myself...

  • Adam, I agree--doing a restore AND a DBCC CHECKDB of backups on a frequent, regular basis is fundamental.  This should be done at least weekly, if not daily.

    Dittos for diff & log backups.

    I advocate regular PIT exercises by all relevant personnel as well.  The time to learn how to put out a fire isn't when your home is burning down.

  • @Adam: I agree completely.  The percentage of respondents who exercise that degree of diligence is discussed in Paul's post (I wanted to "tease" it, not plagiarize it *g*).  Given the numbers I've quoted already, though, you can imagine that the numbers are.. well..  frighteningly small.

    @Jimmy: +1 on the regular PIT exercises, as well as the restore-and-DBCC regime.  Our approach on each server was to cycle through two production databases a day, with system database done daily (why do people forget system databases?!?).  We'd end up going, at most, 6 days between databases.

    We used to say, "don't practice in front of the CIO."  I like your metaphor too.

  • Jimmy brings up a good point with regard to the PIT test: customers should do FULL disaster tests--which may or may not include a PIT restore, depending on the scenario.  I've been involved in one such exercise, and it was a fantastic learning experience for everyone involved.  And the sad part is, with regard to the numbers, that I've only ever been involved in one such exercise.  Which is not for lack of trying; customers, especially at the executive management level, where buy in is really required to do something like an all hands on deck disaster test, just don't want to hear about it...

  • I’m a little late to the party here, but this is pretty apropos of some of our discussions here lately.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment