Copy off original data, even if it is corrupt - before restoring backups?

  • Recently experiencing OS/SQL losing connection to my transaction log drive which resides on a SAN. Production DB was marked as suspect yesterday. Luckily, restarting SQL brought it back.

    I keep nightly full backups, diffs at 8 hour intervals, and translog backups each 15 minutes. My company has no tolerance for downtime, much less data loss.

    If/when I run into this again and I am unable to bring it back online - should I take any measures to copy off the original data, even if it is corrupt? Possibly rename the corrupt db? I am always paranoid - even if it means longer down time.

    My fear is that a backup may be corrupt for whatever reason and data is lost. Normally, when I restore db's, I delete the existing and create the new from a backup - in testing and dev environments.

    What would you recommend for my situation? This prod DB is currently 300gb.

    Thanks

  • After putting some thought into it. I can create a test db, restore it from my full backup, diff, and transaction log backups. Once I validate the data/restore, I can drop the corrupt production database. Then rename the test db to production.

  • That works, but even on a fast san that'll take you 60 minutes... when apparently you have 0 tolerance.

    You need to dig much deeper into your dr plans.

  • This doesn't look to me llike a DR question, much more of a backup/recovery question.

    DR planning usually includes a secondary remote environment that will take over production activity when primary environment is down. E.g. "D"isaster in primary location, "R"ecovery in secondary location.

    Actually, I do prefer to call it Business Continuity planning rather than DR planning.

    Having said that, after reading the posting I'm assuming this is either a very small database or you have a great deal of spare space in your storage subsystem.

    Also, not sure why a full backup is needed every 8 hours - this is impacting performance. How much t-log is generated every eight hours? every 24 hours?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • SkyBox (5/5/2011)


    What would you recommend for my situation?

    Get someone in to advise you on a high-availability setup.

    Zero downtime is near-impossible. Close to 0 downtime is expensive, the closer to 0, the more expensive it becomes. If you have near-0 downtime allowance, you cannot be depending on backups for recovery, you need some form of high availability setup. What precisely depends on your available hardware, budgets and a whole lot of other factors

    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
  • I forgot who said that to his boss / client but it went something like this.

    We don't want any downtimes.

    Sure, how many Millions do you have budgeted for this solution?

    Errm, what can we get for X that can be back up running in 15 minutes?

  • PaulB-TheOneAndOnly (5/6/2011)


    Having said that, after reading the posting I'm assuming this is either a very small database or you have a great deal of spare space in your storage subsystem.

    Also, not sure why a full backup is needed every 8 hours - this is impacting performance. How much t-log is generated every eight hours? every 24 hours?

    The db is only 300gb. My company is acutally in the middle of a system conversion and we have only converted over 2 companies to the new system/db. Biggest company goes in at the end of the month which should double the db size, at least.

    I am running FULL backups nightly, differentials at 6am, noon, and 6pm. Translogs every 15 minutes. I may have to modify my backup plan as the size increases.

  • SkyBox (5/7/2011)


    I may have to modify my backup plan as the size increases.

    If you need zero downtime, or anything close to that, a backup plan is not sufficient. It's part of the plan, but it's not the whole. You need some form of HA/DR. What specifically depends on your budget, your downtime allowances, applications and a whole bunch of other factors.

    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
  • My dept. is over budget at the moment, so I have to work with what I have for now.

    Would like to get a feel for how often SQL databases actually have to be restored in production environments - outside of developers incorrectly updating tables.

    I understand there are serveral variables involved, but some of you more experienced dba's might be able to give me some sort of an idea.

  • Generally only in the case of a disaster (fatal corruption, drive failure, server failure, etc)

    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

Viewing 10 posts - 1 through 9 (of 9 total)

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