Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SkyBox
SkyBox
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 736
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
SkyBox
SkyBox
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 736
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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
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.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
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


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
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?
SkyBox
SkyBox
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 736
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
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


SkyBox
SkyBox
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 736
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search