Move dbs Identify Jobs

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

  • 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





    from dbo.sysjobs j

    inner join dbo.sysjobsteps s on s.job_id = j.job_id

    order by, s.step_id


    JobName =

    ,JobEnabled = j.enabled

    ,JobDesc = j.[description]

    ,ScheduleName =

    ,ScheduleEnabled = sched.enabled



    ,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'


    ,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)'


    ,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'


    ,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'


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





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