September 16, 2008 at 5:01 am
Hi,
I just wanted others views on the scenario below, lets assume we have a SQL 2K5 box, running a number of user databases.
The following tasks have to be completed;
Backup the system databases (once a week)
Optimize and full backup of the user databases (once a week)
Differential backup of the user databases (every evening expect when the full back runs)
The question is, is there a best practise method to approaching this?
i.e Do I have 1 maintenance plan with a number of sub plans or is it better for me to set them out to be 3 separate plans, 1 for system, 1 for user (full) and 1 for user (diff) and then use the sub plans to separate out out functions. I.e in the optimise and full backup plan, I could have 2 sub plans, one dealing with the optimization and then one for the backup.
Any comments?
September 16, 2008 at 5:32 am
I personally find Maintenance Plans fine on small shops running small databases.
For a large environment I personally prefer to have full control so I usually write my own procs and schedule them as needed.
In regards to your backup/recovery strategy, it has to be aligned with your Business requirements so I really can't tell if it is good or not.
_____________________________________
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.September 16, 2008 at 7:24 am
I basically agree with Paul that I have typically created my own jobs for maintenance. More out of habit than anything being wrong with Maintenance plans. I would personally break out the maintenance plans based on System and User DB's. The only thing I see missing is log backups for the user databases. What if the problem occurs between differentials? Then up to a day's worth of data could be lost.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2008 at 7:48 am
Hi guys,
Thanks for the feedback.
With regards to the transaction logs, my mistake, missed them off the post, it was only an example scenario.
Thanks again for the feedback.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply