September 24, 2015 at 8:56 am
Problem:
My daily backups are running daily when they should be running monthly. Creating a size issue on our sql server.
Setup:
I have a full backup that runs on a schedule once a month, differential that runs daily, and transaction logs that also runs daily. All backups and logs are cleaned up on a weekly schedule. I realize that my cleanup schedule goes against my monthly backups, but it doesn't quite explain why it runs daily when the schedule is set to monthly.
What I've tried to remedy was to split up the databases so they weren't all grouped together, recreate the maintenance plan, and mess around with the schedule without success.
Thanks in advance for all tips!
December 25, 2015 at 10:46 pm
share the script or screenshot of your maintenance plan.
December 26, 2015 at 2:54 am
December 26, 2015 at 12:42 pm
I'd review the schedule to see if your full backups are frequent enough and if you're keeping enough of them. Review your recovery point objective to make sure you're okay with your log backups. While you're at it, test them to make sure they're viable because if they aren't, they're of no use to you.
Beyond that, take a look at alternatives to the maintenance plans. They're better than nothing, but that's about it. I've heard good things about Ola's scripts, Minion and Red Gate, but I haven't used any of them. In any event, get rid of the maintenance plans.
December 26, 2015 at 4:12 pm
stephend 55510 (9/24/2015)
I have a full backup that runs on a schedule once a month, differential that runs daily, and transaction logs that also runs daily. All backups and logs are cleaned up on a weekly schedule.
First of all, DIFs are cumulative since the last FULL backup. Unless the database is subject only to very light modifications of data and very few inserts, they could easily become very much larger than just doing FULL backups, especially since you're only doing one full backup per month. Not sure you're saving anything by doing DIFs here except near the beginning of the month.
Before I comment further on your backup plan, how big is the database (GB) and how big have the daily log file backups been?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2015 at 4:28 pm
Piling on Jeff's good comment, unless there is a compelling reason I wouldn't suggest this great interval between the full backups, because the fact that the less one has to play forward when recovering from a disaster the better. Further on this, running the transaction log backups at the same interval as the differential backups simply does not make sense, if a disaster falls on the wrong side of the backup timings you will loose a whole day.
Suggest that you either post here the full details including sizes, medium, growth rates etc., in fact anything that may be relative to the subject or bring in an expert on the subject, frankly I wouldn't want to be in your shoes if s**t hits the fan.
😎
December 26, 2015 at 4:45 pm
Interesting metric... How much sh*t do you feel comfortable being covered with when it hits the fan? More frequent log backups and full backups minimize the stink...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply