June 11, 2013 at 3:27 am
I’m embarking on a project to migrate some dbs in my environment. Moving the db logins etc is straight forward. But how do I make sure the relevant jobs are moved to.
Can MSDB be queried or do I need to set up profilwer.
June 12, 2013 at 9:14 am
hi,
yes, you can query msdb-database. There are some system tables there. At the end there are two selects for getting the most important information. All the tables are described in books online, so have a look there.
Another possibility: Have a look at the jobs in the management studio. There a script for creating the jobs can be created. This way you just have to mark the jobs to transfer, let the script create, and execute it at the destination instance.
use msdb
select
j.name,
s.step_id,
s.step_name,
s.command
from dbo.sysjobs j
inner join dbo.sysjobsteps s on s.job_id = j.job_id
order by j.name, s.step_id
select
JobName = j.name
,JobEnabled = j.enabled
,JobDesc = j.[description]
,ScheduleName = sched.name
,ScheduleEnabled = sched.enabled
,jsched.next_run_date
,jsched.next_run_time
,FreqType = case sched.freq_type
when 1 then 'One time only'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly, relative to freq_interval'
when 64 then 'Runs when the SQL Server Agent service starts'
when 128 then 'Runs when the computer is idle'
end
,FreqInterval = case sched.freq_interval
when 1 then 'once (freq_interval is unused)'
when 4 then 'daily (Every freq_interval days)'
when 8 then 'weekly (freq_interval is one or more of the following: 1 = Sunday 2 = Monday 4 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday)'
when 16 then 'monthly (On the freq_interval day of the month)'
when 32 then 'monthly (freq_interval is one of the following: 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = Saturday 8 = Day 9 = Weekday 10 = Weekend day)'
when 64 then 'starts when SQL Server Agent service starts (freq_interval is unused)'
when 128 then 'runs when computer is idle (freq_interval is unused)'
end
,SubdayType = case sched.freq_subday_type
when 1 then 'At the specified time'
when 2 then 'Seconds'
when 4 then 'Minutes'
when 8 then 'Hours'
end
,FreqSubdayInterval = sched.freq_subday_interval -- Number of freq_subday_type periods to occur between each execution of the job.
,FreqRelativeInterval = case sched.freq_relative_interval
when 0 then 'freq_relative_interval is unused'
when 1 then 'First'
when 2 then 'Second'
when 4 then 'Third'
when 8 then 'Fourth'
when 16 then 'Last'
end
,sched.freq_recurrence_factor -- Number of weeks or months between the scheduled execution of a job. freq_recurrence_factor is used only if freq_type is 8, 16, or 32. If this column contains 0, freq_recurrence_factor is unused.
,sched.active_start_date
,sched.active_end_time
,sched.active_end_date
,sched.active_end_time
from dbo.sysjobs j
left outer join dbo.sysjobschedules jsched on
jsched.job_id = j.job_id
left outer join dbo.sysschedules sched on
sched.schedule_id = jsched.schedule_id
order by j.name, jsched.next_run_date, jsched.next_run_time
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply