Do You Verify Your Database Backups?

  • Comments posted to this topic are about the item Do You Verify Your Database Backups?

    Brad M. McGehee
    DBA

  • I absolutely agree... It's not what you can backup... it's what you can restore. I do at least one "practice" recovery a week and I'm not the System DBA. I do it because I'm pretty sure that they don't do it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • To be honest I am shocked to hear that people don't verify their backups.

    I'm also surprised that it isn't the default action for a backup and requires a DBA to deselect it if they don't want to verify.

  • What backups?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • David.Poole (2/22/2009)


    To be honest I am shocked to hear that people don't verify their backups.

    I'm also surprised that it isn't the default action for a backup and requires a DBA to deselect it if they don't want to verify.

    What's worse is that, on 2000 and earlier, verify did very little. It checked the backup headers and a bit more. Passing verification was not a guarantee that the backup could be restored.

    Even on 2005 and higher, I think you need to use the Checksum option for a verify check everything. Without that, the only way to check that a backup can be restored is to restore it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • In 2000 I checked it, but didn't count on it. I tended to restore often enough to dev/test that I didn't worry about scheduling things too much. We did have a quarterly test on some of the more critical systems to be sure that we could do it, and we'd pull back from tape at least yearly if not more often to be sure that things were working correctly.

    I think it depends on your environment, but you want to both practice your skills at restoring (so restore logs, backups, and to times), and also make sure your equipment is working.

  • We're lucky. We don't have a true data warehouse so to provide reporting capabilities without impacting the OLTP we restore nightly the backups into the DW. The big thing missing though is the TLog and tape copies. The one time I tried to test the tape copies the tape was found to be corrupted!!!! I now just need to convince the system admin team (owners of the tape backups) that they need to put in place testing of the tapes. They say they have no room to test the recovery, I say a backup that cannot be recovered is not a backup. Discussions (arguments) continue ...

  • We test them daily as they are transferred and restored to another server.

    This another server is called "the clone".

    Therefore anyone (testers, developers, ...) can do tests, simulation, analysis,... on the data of the production environment without touching it.

    JM.

  • We don't need no DBA 'round here checkin' our backups. We got that fancy Squeal Server that takes care of itself. Now git.


    James Stover, McDBA

  • The backup is not complete until I've restored it on a test machine. I know not everyone has this capability but since I've always been on small teams that were doing development as well as maintaining the production enviornment I have always had a "development" and "Test" machine and can not imagine relying on a backup that has not been tested.. The best axiom is "It isn't what you can backup, it's what you can restore".

    James.

  • I'll try the contrarian view today, just to see..! I wonder if a better question isnt "how often do you verify" your backups?

    I manage a couple small businesses, and the reality is I test backups perhaps once a quarter. Losing a quarters worth of information would definitely hurt, but it would be survivable. Why don't I test more often? Time, and experience. I have a limited amount of time each week, and a lot of things to do - that combined with my experience telling me that bad backups are pretty rare, I can extend the risks I take, with an understanding of the dangers.

    That's how it is with many small businesses. Even if you have a DBA, do they really need to verify every backup? Even if you do one a week, you potentially have a 7 day gap where you might have the ability to restore to point in time. Is that acceptable?

    Why doesn't MS make verification automatic, or include a step in the maintenance plan for restoring a test copy and checking that it's valid?

  • I admit, I don't verify all my backups. Why. I support 80 + SQL Servers, am expected to support 2 large Oracle apps (Peoplesoft and Cerner), 4 Sybase servers, I suppose the My SQL is my baby, I have to pass .NET certification, write Access reports, Medical manager reports, support legacy HP-3000 apps, Star Vista reports (base on a MUMPS database...very interesting). And handle end user questions/problems.

    Am I a disaster waiting to happen. You betcha. I think management doesn't think the dbas (2 of us) do much. What can you do but your best.

  • For us newbies - Would someone please give a hint as to how you verify a database backup ?

  • I test my backups on a daily basis by automatically restoring on a test server. Early on in my career I didn't do this, but read enough articles and forums like SSC that I got scared into doing it. 🙂

  • I do semi-random spot checks. I restore one or two backups a week to test them, picking different databases different times. I've never yet had one fail, but I still feel the need to spot check.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 56 total)

You must be logged in to reply to this topic. Login to reply