SQL Agent Jobs

Hello Guys,

I had question regarding resuming SQL Agent Jobs.
The problem that I'm facing is because of these jobs.
Actually every Saturday and Sunday, the servers in our environment are rebooted because
of patching or some other activity. Because of these the agent jobs that are scheduled on these servers
are stopped. Jobs which are usually getting affected because of these are full and differential backups.
My question is, Is there any possible way by which I can resume these jobs which are interrupted because of
server restart? Meaning as soon as the server is restarted and the SQL services are online, I want that the point at which the jobs stopped should resume again and execute until unless there is any failure or some other problem.

Kindly look into this issue and please give your comments.

Thanks in advance.
Abhinav SinghSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can have a job that runs when SQL Server Agent starts (on the Schedule Type of that job chose "Starts automatically when SQL Server Agent starts") but I think you should review the process of shutting down the database servers.
Try to set the jobs to run before or after the patch application. Talk with the system administrator team to define a better strategy.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abhinav SinghSoftware EngineerAuthor Commented:
See, if I use "Starts automatically when SQL Server Agent starts", then I won't be able to schedule it for every day or every week. Currently the backup jobs has been scheduled to execute on daily basis.
We tried coming up with better strategy, but that too is not working out.
If you could just tell me that there is any other way or some query using which we could find the job was stopped and make it execute again from where it stopped, then it would be helpful.
Thanks for replying
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I meant to create a new job that every time SQL Server Agents starts it will check the status of the backup jobs. If those jobs were cancelled that it will run them again. Makes sense for you?
0
jimtpowersCommented:
There isn't any way for a backup job to pick up where it left off. Vitor's suggestion is correct. The job just has to start again at the beginning.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Yes, if you cannot control when the server is rebooted, then you need to have a job that starts up when Agent starts up and checks the latest SQL backup job to see if the latest status is complete or not and act appropriately.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.