Copy off original data, even if it is corrupt

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Disaster Recovery » Copy off original data, even if it is corrupt...

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

Rate Topic

Display Mode

Topic Options

Author

Message

SkyBox

Posted Thursday, May 05, 2011 3:20 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:12 PM
Points: 146, Visits: 549

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

Post #1104236

SkyBox

Posted Thursday, May 05, 2011 7:50 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:12 PM
Points: 146, Visits: 549

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.

Post #1104295

Ninja's_RGR'us

Posted Thursday, May 05, 2011 7:59 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 21,357, Visits: 9,532

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.

Post #1104297

PaulB-TheOneAndOnly

Posted Friday, May 06, 2011 1:00 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:03 AM
Points: 2,979, Visits: 4,389

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.

Post #1104838

GilaMonster

Posted Friday, May 06, 2011 1:23 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

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 2008, MVP
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

Post #1104852

Ninja's_RGR'us

Posted Friday, May 06, 2011 1:43 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 21,357, Visits: 9,532

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?

Post #1104861

SkyBox

Posted Saturday, May 07, 2011 11:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:12 PM
Points: 146, Visits: 549

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.

Post #1105028

GilaMonster

Posted Saturday, May 07, 2011 12:09 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

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 2008, MVP
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

Post #1105036

SkyBox

Posted Saturday, May 07, 2011 12:30 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:12 PM
Points: 146, Visits: 549

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.

Post #1105044

GilaMonster

Posted Saturday, May 07, 2011 12:39 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1105047

« Prev Topic | Next Topic »

Permissions