Query to rerun missed jobs after a backup and restore.

Does anyone know of a Query I can run after restoring a db that will go
though and rerun all the jobs that were missed between the time of the
final differential pulled and the time the differential was restored. I
have about 50 jobs and it's really annoying to go through each job
and run it manually.
SQL 2000 SP4
Thanks BTWWell If no one knows of a procedure, then I will have to create one,
but I will need help with the logic.
Right now it looks like all the information I require is stored in two
tables sp_help_job & sp_help_jobschedule this can be joined on the
job_id and will provide the last run date and time as well as the
intervals.
What I Am looking at is to rerun all jobs that occurred in the past,
that need to be run again because they were missed during time of the
last differential and the final restore.
The Logic:
I know the current time, and I know when the next job is to be run. So
what I need to do is first get the current time, and compare it to the
last executed time. If the job interval is 15 minutes or less, don't
run the job. If the job time is greater the specified duration, run the
job.
I hope that makes sense.
-Matt-|||Let me see if I understand your probem. Are you asking what jobs would have
executed in a certain time window in the past, assuming all the schedules
have stayed the same? I suppose you can write some logic that would
duplicate our schedule calculation, but it's probably worth trying to
understand better how come those jobs have been missed. Did you shut down
agent during the time window when you did backup/restore?
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew" <MKruer@.gmail.com> wrote in message
news:1144951366.394182.197090@.i40g2000cwc.googlegroups.com...
> Well If no one knows of a procedure, then I will have to create one,
> but I will need help with the logic.
> Right now it looks like all the information I require is stored in two
> tables sp_help_job & sp_help_jobschedule this can be joined on the
> job_id and will provide the last run date and time as well as the
> intervals.
> What I Am looking at is to rerun all jobs that occurred in the past,
> that need to be run again because they were missed during time of the
> last differential and the final restore.
> The Logic:
> I know the current time, and I know when the next job is to be run. So
> what I need to do is first get the current time, and compare it to the
> last executed time. If the job interval is 15 minutes or less, don't
> run the job. If the job time is greater the specified duration, run the
> job.
> I hope that makes sense.
> -Matt-
>|||Thanks for the reply.
The reason why the jobs were missed is simple. The databases in
question are being replicated in a live environment. So we are making a
backup of the database, restore that copy to a different system, and
then do a differential on the database. The problem lays in the doing
the restore for the differential. During a differential restore there
is a period of time (depending on the size) that jobs can run and not
be on the diff, hence there was a change made on one database and not
the other one. This is where the job would need to be run. These
databases are running 24/7 with tens to hundreds of gigs of data, and I
can literally say that delaying a diff for more then 2 days, its faster
to pull a new full, there is just that much data being moved/updated.
Somewhat off topic. I really hate the way the DB stores the date and
time. This would be so much easier if the date/time was a single
integer.|||BTW I think I have all the information I need using this Join
Statement.

code:
SELECT dbo.sysjobs.job_id, dbo.sysjobs.name, dbo.sysjobs.enabled,
dbo.sysjobservers.last_run_outcome,
dbo.sysjobservers.last_outcome_message,
dbo.sysjobservers.last_run_date,
dbo.sysjobservers.last_run_time, dbo.sysjobservers.last_run_duration,
dbo.sysjobschedules.next_run_date,
dbo.sysjobschedules.next_run_time,
dbo.sysjobschedules.freq_recurrence_factor,
dbo.sysjobschedules.freq_type,
dbo.sysjobschedules.freq_interval,
dbo.sysjobschedules.freq_subday_type,
dbo.sysjobschedules.freq_subday_interval
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id =
dbo.sysjobschedules.job_id INNER JOIN
dbo.sysjobservers ON dbo.sysjobschedules.job_id =
dbo.sysjobservers.job_id
Where last_run_outcome != '1' and last_run_outcome != '5' and
dbo.sysjobs.enabled = '1'
Order by dbo.sysjobs.name