October 7, 2008 at 8:24 am
Hi All,
I have a query relating to automating jobs in sql server agent.
A copy of our live financial database is made at the beginning of every month (on workdays only) and holds data as at the end of the previous month.
The process of starting the restore job is done manually. After this job has been completed we have a number of other jobs which build various tables which we have to start and schedule manually.
Is there any way we can automatically start the jobs after the restore has been successfully completed?
The issue is that the month end restore does not happen at the same day or time each month and we cannot simply rely on monthly schedules. We need to run the other jobs at different times after the restore has completed.
Thanks
October 7, 2008 at 11:07 am
The easiest way is to add a step to the restore job that calls msdb..sp_start_job for the job you want to kick off when the restore completes.
Remember that sp_start_job is asynchronous and will return immediately - it will not wait for the job to complete.
October 8, 2008 at 2:16 am
Thanks that may well do the trick
🙂
October 8, 2008 at 2:57 pm
Just as a warning, a job can only be running once at a given time otherwise the sp_startjob will fail.
What I mean by this is that if you have a job called SPECIAL_BACKUP and its already running, starting it again while it is already running causes an error.
There is also a glitch I discovered in SQL 2000 and 2005 if you try to create or modify a job within a transaction and start that job within the same batch, under certain conditions you will get a false error that prevents the job from running.
~Craig
MCDBA, MCITP
Craig Outcalt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply