October 16, 2008 at 11:49 am
Hello everyone.
I have more than 200 databases residing on 3 different clustered systems
Sql 2005 sp2 , windows enterprise 2003
My backup plan is once a day – full backup, every 3 hours – trans log backup
All to disk. Retention is 24 hours, then it gets overwritten.
All old backups are going to tapes.
Here is my question: if I need to recover 1 -5 even 10 databases up to the last trans log backup
It’s not a problem. What if, G-d forbid, I need to recover all 200 databases….. you know, things happen
And I have to restore not only all those 200 full backups(which is OK) but also only G-d knows how many Trans log backups
In the specific sequence and so on…..sound like a nightmare to me
What would you suggest the best backup schedule would be?
I was thinking to run full backup more often, like 3-4 times a day.
I would appreciate any suggestion
October 16, 2008 at 12:49 pm
Your strategy sounds good, though I might suggest running the tran logs more often. If the log drive fails, is the loss of 3 hours data acceptable?
I'd also suggest a 48 hour retention. At the moment, if your latest full backup can't be restored, then you are completely out of options, as you won't be able to restore the full before that and apply 2 days of tran logs.
I have seen scripts that automatically restore the full and then all the tran logs. You may want to consider writing something like that that's generic enough you can use it on all of your DBs. I don't have one handy, but you should be able to find one with a bit of searching (or write from scratch with a little thought)
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
October 16, 2008 at 1:54 pm
If your FULLs take too long, perhaps do a differential(s) during the day to cut down on the number of transaction logs that would need to be restored.
October 17, 2008 at 4:32 am
I see you have implement some sort of a standard backup procedure all accross the board.
In my opinion Backup/Recovery strategy should be customized for each database according to business requirements e.g. SLA
You may find some first tier databases require recovery "to the last transaction"...
a second tier group of databases would be fine with 15 minutes TLOG interval or so...
while a third tier would be just Okay with a Daily or even Weekly or Monthly backup -reporting and archiving databases perhaps?
It all depends on your business requirements. 😉
_____________________________________
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.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply