Technical Article

Compare MS SQL Agent jobs on two servers

,

During a database migration or a side by side database upgrade project we need to migrate the MS SQL Server Agent jobs. This script compare jobs by names and shows unmatched jobs from both boxes. That would help to avoid problems related to the forgotten to migrate MS SQL Agent jobs.

In order to use this script you need to:

  1. create a linked server to the other server
  2. replace {OLD_BOX} with your linked server name
  3. execute the script.

The account which you use to execute this script must have select permissions on the table msdb.[dbo].[sysjobs] on both servers.

select j.name, j2.name
from [{OLD_BOX}].msdb.[dbo].[sysjobs] j
full outer join msdb.[dbo].[sysjobs] j2
on j.name = j2.name
where (j.enabled = 1 or j.enabled is null)
and (j.name is null or j2.name is null)

Rate

5 (2)

Share

Share

Rate

5 (2)