Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Copy off original data, even if it is corrupt - before restoring backups? Expand / Collapse
Author
Message
Posted Thursday, May 05, 2011 3:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:22 PM
Points: 161, Visits: 644
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
Posted Thursday, May 05, 2011 7:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:22 PM
Points: 161, Visits: 644
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
Posted Thursday, May 05, 2011 7:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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
Posted Friday, May 06, 2011 1:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Friday, May 06, 2011 1:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 41,528, Visits: 34,444
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
Posted Friday, May 06, 2011 1:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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
Posted Saturday, May 07, 2011 11:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:22 PM
Points: 161, Visits: 644
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
Posted Saturday, May 07, 2011 12:09 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 41,528, Visits: 34,444
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
Posted Saturday, May 07, 2011 12:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:22 PM
Points: 161, Visits: 644
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
Posted Saturday, May 07, 2011 12:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 41,528, Visits: 34,444
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 »

Add to briefcase

Permissions Expand / Collapse