Apologies if the question does not make any sense.
I have a requirement where i need to restore a live database on to training database on a daily basis.
So i have created a single job which consists of 7 steps with its corresponding 7 schedules. The reason for creating such a job is that we have a backup script created and applied by our previous DBA where it creates a folder for each database with corresponding 7 more folders for each day of the week in the backup folder. So this means we have one full backup for each day but in different folders. I have created and implemented this job for the BI team and was running successfully for the last couple of months. And just noticed today after the BI team reported back saying the data is not changing at all for their reports. So when i looked into the job history i noticed that the job step which was actually supposed to run step 2,3,4,5,6 & 7 on their respective days with their respective schedules is only executing step 1 instead and this has been happening since last week.
But as everyone knows when we execute the job manually we have the privilege of selecting which step to run in multi step jobs and everything works fine.
Now i am not sure why is this not working now and why is it triggering the step 1 instead of the actual step. Please share your thoughts on my approach and is this expected behaviour for multi step jobs.
SQL server 2008 R2
You can find the job steps & schedules screenshots in the attachments.
Thanks in advance....
Answer by Kev Riley ·
When a job is started by a schedule, it will start at the job step that is identified as the Start Step on the Steps page of the Job Properties - in your case I'm guessing this is Step 1.
To have a different step run each day you would need to either have some logic in each step to determine what day it is and then whether to run or not (maybe with extra steps), or more simply have 7 different jobs with 7 different schedules