Fixing Missing SQL Agent Jobs After a Migration

I’ve been doing migrations fairly continuously for the past 18 months. PowerShell has been my primary mechanism for many parts of the process, including copying jobs from the source SQL Server to the target. That has worked almost without incident each time. However, recently, an app team noticed that there were SQL Server Agent jobs missing on their new 2022 SQL Servers.  Because the first couple of missing jobs also existed on their Development environment, they were able to recreate those jobs in production. They naturally expressed concern that other jobs may be missing.

How did we get here?

The first question I asked was, “How did we get here in the first place?” The process for copying jobs successfully depends on a couple of things, such as:

• Is the job owned by a principal that exists on the target? If not, the copy is going to fail.
• Database context in a SQL Agent job may often be set to a user database. From that context the Command section of the job will run. Does that database exist on the target?
• Is there an Agent Operator used in the source job? If so, that operator needs to exist on the target first.

Agent jobs are copied with code that looks similar to the below. Notice that I’m using the switch -ExcludeDisabledJobs to ensure I’m only copying jobs that are needed. I still don’t really understand why some jobs didn’t copy. As far as I know, I didn’t hit any of the above issues when the copy was performed. I also don’t recollect seeing any output in PowerShell telling me certain jobs didn’t copy.

$source = 'SourceInstanceName'
$Destination = 'TargetInstanceName'

get-dbaagentjob -sqlinstance $Source -ExcludeDisabledJobs | copy-dbaagentjob -source $Source -Destination $Destination

This works great, usually, but like I said, there were at least some jobs that were missing.

What was the remedy?

So, how did I solve this? Well, usually instance level object definitions are exported to .sql files using the below code:

$sqlinstance = 'Sqlinstancename'
$Path = 'C:\DBATools\InstanceExport\'

Export-DbaInstance $sqlinstance -Path $Path

This PowerShell will export all server level object definitions to .sql files. So, in theory, I could have found the file for SQL Agent, opened it and done a compare between the jobs in that file and what was on the target. Once I had done that, then I could run the sections of the .sql script on the target that would recreate the missing jobs.

There was one problem. For some reason, that step was missed and the ec2 instances had already been destroyed. Now, I suppose if we had a snapshot of the machine, then the ec2 instance could have been recreated from the snapshot. Once that was done by a Cloud Admin, then we could write a query or use PowerShell to copy anything that was missing.

Going in a different direction:

I went a different direction when someone reminded me that I could just restore msdb from the source and use that to solve the problem. MSDB contains the SQL Agent information. Jobs, database mail, operators, everything. So, I could use that to do a comparison and get the job definitions for what was missing.

I downloaded the msdb backup from AWS s3. Ola Hallengren’s scripts have been a favorite for my entire career, so it was easy to locate the path I needed. After downloading the msdb backups I needed, I whipped up the needed T-SQL to do the restores under a new name and file location. I used the WITH MOVE command to move the logical files to the same directories as the existing user databases. I also renamed the databases in the T-SQL to msdb_SourceServerName since two databases can’t have the same name on the same instance.

Doing the comparison:

Now that the databases were restored, how to do the comparison was the next step. I used the trusty EXCEPT clause to return all the names of enabled jobs on the source, except the ones that existed on the target. This allowed me to see quickly what Agent jobs were missing. I could have used a LEFT JOIN, but this method seemed like a quicker, easier method to get what I needed. I really like EXCEPT and its related INTERSECT syntax.

SELECT [name]
FROM [msdb_Source].dbo.sysjobs
WHERE enabled = 1

EXCEPT 

SELECT [name]
FROM msdb_Target.dbo.sysjobs;

Now I had a list of Agent jobs on the source that didn’t exist on the target. So now what?

Getting the job definitions:

I went back to the source msdb databases. Below is the complete script. I built most of this on my own, piece by piece as I thought through what I’d need to know.

• Job Name
• Step Names
• The code in the steps
• Whether the job is scheduled or not
• The database context in which the commands ran
• The frequency of the job schedule and what time of day
• Next Run Date so I could determine what day of the week the job runs on

After I got the basics, I turned to this article on MSSQLTips to help me get the frequency information.

DECLARE @JobName VARCHR(100)
SET @JobName = 'JobNameFromTheList'

SELECT J.[name], [sDBP].[name] AS [JobOwner],
SUSER_NAME(J.owner_sid) AS JobOwnerName
,J.[enabled], J.[description], JS.step_id, JS.step_name, JS.subsystem, JS.command, 
JS.[database_name] AS JobStepDatabaseContext,
CASE
        WHEN [SysSched].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled],
SysSched.[name] AS ScheduleName,
JSched.next_run_date, 
CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' ' 
                     + CASE [freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' 
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                   + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]
FROM msdb_Source.dbo.sysjobs AS J
LEFT JOIN msdb_ Source.dbo.sysjobsteps JS ON J.job_id = JS.job_id
LEFT JOIN [msdb_ Source ].[sys].[database_principals] AS [sDBP]
        ON [J].[owner_sid] = [sDBP].[sid]
LEFT JOIN  msdb_ Source.dbo.[sysjobschedules] AS JSched ON J.job_id = JSched.job_id
LEFT JOIN msdb_ Source.dbo.sysschedules SysSched ON JSched.schedule_id = SysSched.schedule_id
WHERE J.[name] = @JobName;

Here was my process for creating the missing jobs:

• I plugged in the first job name value from the comparison query and got the values I’d need to recreate that job.
• I manually created that first job in SSMS, and then scripted out the job.
• Opened the job in SSM and removed the schedule that wasn’t relevant for that job.
• Used the output from the above query to create the needed schedule name, frequency, etc on the job in SSMS and save.
• For the next job, I ran the above script to retrieve the job details, and then replaced the values in the scripted job from bullet 2 like the Job Name, Job Step Names, job description, and the code in the Command section. I ran that, and then I repeated bullets 3 and 4 to get the right schedule on the newly created job.

This saved me some time and clicks because I wasn’t recreating each missing job from scratch in the SSMS GUI. Is it a perfect process? No, but it would take me far less time to get the missing jobs in place this way than it would to try to completely automate their creation.