Bi-week... semi-week... fortnight... "every two weeks"
Recently on the SQL Community Slack group, someone asked if there was a way to schedule a backup to run on alternate weeks. I'm not exactly sure why (maybe they wanted to run full and differential backups on alternating weeks), but yes, we can do that. In part one of this two part series, we'll explore how to do just that via the GUI in SSMS. In part two, we'll look at how to automate that.
Put it on my calendar
With all of options in the UI, scheduling jobs via the SQL Server Agent can look daunting at first glance. But, like many things, if we take it a step at a time we can figure it out. Let's start at the top and work our way down.
- Name: Choose a name for your schedule. My preference is to name the schedule for what it is doing rather than any notion of when it runs. That way, if/when the details of the "when" change we don't also need to remember to change the schedule name. Or, worse, forget to rename the schedule and then have the name not reflect the reality. Either way you go though, the choice is up to you.
- Schedule Type: of the options available (start automatically when SQL Server Agent starts, start whenever the CPUs become idle, recurring, or one-time), recurring sounds like the most appropriate.
- Frequency: Here you have a couple of options that will work. You can either specify a value of "Daily" for the Occurs field and "14" for Recurs every (given that there are 7 days in a week, 14 = 7\*2). Or choose "Weekly" for Occurs, "2" for Recurs every, and then choose the day of week on which you'd like the job to run. For this example, I'm going to choose Friday at 20:00 (8:00 PM if you're more accustomed to that). I prefer the latter approach as it's easier for me to, at a glance, determine what day of week the job runs.
- Duration: This will specify when the schedule goes into effect and when to stop executing it. I created my example on 2023-10-18 so I'm choosing that for my start date. Note - since I specified that this schedule will run on Fridays in the previous step, this job will first run on 2023-10-20 (i.e. the first Friday after the start date). And since I want this to run every two weeks forever, I'll choose "No end date".
Once it's put together, it looks something like this
See you in two weeks!
Once we hit "OK", we've got the schedule we wanted for the full backups. Armed with this knowledge, do you think you could create the corresponding schedule for differential backups such that the full and differential backups run on alternating weeks? I encourage you to give it a shot!