September 19, 2013 at 6:20 am
Can I setup a single agent job to handle Full(Every Sunday at 00:00), Differential(Everyday at 00:00) and log backups(every 30 mins) in a single job without stored proc logic, or do I need to setup 3 different jobs?
If different jobs are needed because of the different schedules, how can I make sure that jobs don't overlap(I don't need the Diff job at 00:00 if a full backup is running, or a log backup when I also do either of the previous two). I cant seem to figure this out in the job scheduler
Thanks.
September 19, 2013 at 7:41 am
You can do this, but it's not simple.
You need a job with three steps, one for each type of backup. Then you need a schedule that will run (at minimum) every 30 minutes.
Within each job step, you need to do a date/time check to verify when the last time the backup of choice was run (you don't need this for the transaction backup as you've granulated the schedule down that far). Within the IF...ELSE block you create for this, you run the backup or not.
You can have multiple schedules created for a single job and just have the job check which schedule started the job to identify which backup step needs to run too.
September 23, 2013 at 11:35 am
Thanks for the reply!
I am still unsure about the best practice for using agent jobs to make routine log, differential and Full backups.
Should I do three different jobs, one for each? If so, how do I manage the problem of skipping a log backup if there is either a Diff or full backup also scheduled or skipping a Diff backup if there is a full backup scheduled? Can I setup a schedule to do a log backup every hour except 00:00?
What is the best practice to do this with sqlagent, as this must be a common requirement? Is a hardcoded date and time the only option?
Thanks for the help
September 23, 2013 at 11:48 am
This is no real best practice. And most people don't worry about the Transaction logs hitting at the "same time" as the Differential or Full backups because they deliberately create schedules where the backups don't happen at the same time.
Such as the Full backup every Sunday at 12:00 a.m., the Differential happens Monday through Saturday at 12:00 a.m., and the Transaction Log happens during business hours every 30 minutes.
If you have a true 24 / 7 shop, then set the TransLog backups to occur every 30 minutes between 1:00 a.m. to 11:30 p.m. Or, offset the times. Instead of running on the 00 and the 30, run them at the 15 and 45 or 20 and 50, like that. (think minutes with that last statement).
EDIT: How are you creating these backups? Are you using T-SQL or are you using Maintenance Plans? Or do you have a third party tool for backups?
September 24, 2013 at 3:18 am
It all depends on how is your environment. It would be easy to maintain if you create a seperate job catagarize by the type of backup i.e. full, diffrential & Tlog.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
September 24, 2013 at 7:09 am
What is the reasoning behind having one job for all the backup types? Having three jobs is a lot easier to build and administer.....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy