1 SQL daily restore job to run 7 days a week which consists of 7 steps & their respective 7 schedules

Question

question

S_M asked Sep 5, '18

1 SQL daily restore job to run 7 days a week which consists of 7 steps & their respective 7 schedules

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....

sql-server-2008-r2 job sql agent schedule

job-steps.png (16.0 KiB)

job-schedules.png (16.6 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-09-06T07:29:21Z

Kev Riley answered Sep 6, '18

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

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

S_M commented · Sep 06, 2018 at 11:17 AM

Thanks for your response @Kev Riley, Implementing an additional step with some logic seems a bit complicated as of now, so i will go for the alternate option of creating seperate jobs which is more simpler way to handle the situation as per your suggesstion.

0 ·

question