May 5, 2011 at 3:20 pm
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
May 5, 2011 at 7:50 pm
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.
May 5, 2011 at 7:59 pm
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.
May 6, 2011 at 1:00 pm
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.May 6, 2011 at 1:23 pm
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
May 6, 2011 at 1:43 pm
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?
May 7, 2011 at 11:51 am
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.
May 7, 2011 at 12:09 pm
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
May 7, 2011 at 12:30 pm
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.
May 7, 2011 at 12:39 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply