Recovering a Deleted Agent Job From an msdb Backup

A deleted agent job can come back from an msdb backup. Restore that backup under a new name, never over the live msdb. Then read the job from the copy and recreate it.

Why you never restore msdb over itself

Picture a Monday morning. Someone tidied the job list on Friday and deleted the wrong job. Nobody scripted it. The nightly load missed the whole weekend, and now the phone is ringing.

The tempting fix is to restore msdb from last night’s backup. Please don’t. A restore rolls every job, schedule, alert and history row back to the moment of the backup. You would fix one job and break ten.

The safer way is to restore the backup as a second database under a different name. Then you read the old job from the copy and rebuild only that job.

Build a job worth losing

The demo creates a job named DemoNightlyLoad with two steps and a 2 AM schedule. It starts out disabled, so the schedule stays quiet. The demo creates server-level objects (a job in msdb, a restored copy and a backup file) and removes all of them at the end.

DROP DATABASE IF EXISTS MsdbCopyDemo;
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DemoNightlyLoad')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DemoNightlyLoad';

EXEC msdb.dbo.sp_add_job @job_name = N'DemoNightlyLoad', @enabled = 0,
     @description = N'Loads the staging tables every night.';

EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoNightlyLoad', @step_id = 1,
     @step_name = N'Load staging', @subsystem = N'TSQL', @database_name = N'master',
     @command = N'SELECT 1 AS StagingLoaded;',
     @on_success_action = 3, @on_fail_action = 2;

EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoNightlyLoad', @step_id = 2,
     @step_name = N'Check row counts', @subsystem = N'TSQL', @database_name = N'master',
     @command = N'SELECT 2 AS CountsChecked;',
     @on_success_action = 1, @on_fail_action = 2;

EXEC msdb.dbo.sp_add_jobschedule @job_name = N'DemoNightlyLoad',
     @name = N'Every night at 2 AM', @freq_type = 4, @freq_interval = 1,
     @active_start_time = 20000;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'DemoNightlyLoad', @server_name = N'(local)';

Now look at the job as it stands. Both steps show enabled 0. Step 1 has on_success_action 3 (go to the next step) and on_fail_action 2 (quit with failure). Step 2 has 1 (quit with success) and 2.

SELECT j.name, j.enabled, s.step_id, s.step_name, s.on_success_action, s.on_fail_action
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE j.name = N'DemoNightlyLoad'
ORDER BY s.step_id;

Back up msdb, then delete the job

In real life you find last night’s msdb backup. Check its date first. A job created after that backup is not in it.

Here I take the backup myself. COPY_ONLY keeps it out of your backup plan. The default backup folder has no final backslash, so I add one.

DECLARE @Backup nvarchar(400) =
    CONVERT(nvarchar(400), SERVERPROPERTY('InstanceDefaultBackupPath')) + N'\MsdbDemo.bak';

BACKUP DATABASE msdb TO DISK = @Backup WITH COPY_ONLY, INIT;

Now play the careless colleague. The count returns 0, so the job is gone.

EXEC msdb.dbo.sp_delete_job @job_name = N'DemoNightlyLoad';

SELECT COUNT(*) AS jobs_named_demo
FROM msdb.dbo.sysjobs
WHERE name = N'DemoNightlyLoad';

Restore the backup under a new name

The live msdb files are in use, so the copy needs its own file names, which MOVE handles. The logical names are MSDBData and MSDBLog. On a backup you did not make, RESTORE FILELISTONLY lists them.

SELECT name, type_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'msdb');

DECLARE @Backup nvarchar(400) =
    CONVERT(nvarchar(400), SERVERPROPERTY('InstanceDefaultBackupPath')) + N'\MsdbDemo.bak';
DECLARE @DataFile nvarchar(400) =
    CONVERT(nvarchar(400), SERVERPROPERTY('InstanceDefaultDataPath')) + N'MsdbCopyDemo.mdf';
DECLARE @LogFile nvarchar(400) =
    CONVERT(nvarchar(400), SERVERPROPERTY('InstanceDefaultLogPath')) + N'MsdbCopyDemo.ldf';

RESTORE DATABASE MsdbCopyDemo FROM DISK = @Backup
WITH MOVE N'MSDBData' TO @DataFile, MOVE N'MSDBLog' TO @LogFile, RECOVERY;

Read the full definition from the copy

The job is back in the copy, still enabled 0, with its steps, commands and routing. The schedule shows freq_type 4 (daily), an interval of 1 and a start time of 20000. That integer is 2:00:00 AM written as HHMMSS.

A recovered row is only part of a working job. Also check the owner, notifications and any proxy a step used. This demo job has none, so your real job may need more care.

SELECT j.name, j.enabled, j.description, SUSER_SNAME(j.owner_sid) AS owner_name
FROM MsdbCopyDemo.dbo.sysjobs AS j
WHERE j.name = N'DemoNightlyLoad';

SELECT s.step_id, s.step_name, s.database_name, s.command, s.on_success_action, s.on_fail_action
FROM MsdbCopyDemo.dbo.sysjobsteps AS s
JOIN MsdbCopyDemo.dbo.sysjobs AS j ON j.job_id = s.job_id
WHERE j.name = N'DemoNightlyLoad'
ORDER BY s.step_id;

SELECT sch.name, sch.freq_type, sch.freq_interval, sch.active_start_time
FROM MsdbCopyDemo.dbo.sysschedules AS sch
JOIN MsdbCopyDemo.dbo.sysjobschedules AS js ON js.schedule_id = sch.schedule_id
JOIN MsdbCopyDemo.dbo.sysjobs AS j ON j.job_id = js.job_id
WHERE j.name = N'DemoNightlyLoad';

Recreate the job disabled

Now rebuild the job in the live msdb, reading each value from the copy. It stays disabled, because I do not want a recovered job firing at 2 AM before anyone has looked at it.

DECLARE @Description nvarchar(512) =
    (SELECT description FROM MsdbCopyDemo.dbo.sysjobs WHERE name = N'DemoNightlyLoad');

EXEC msdb.dbo.sp_add_job @job_name = N'DemoNightlyLoad', @enabled = 0,
     @description = @Description;

DECLARE @StepId int, @StepName sysname, @Db sysname, @Command nvarchar(max),
        @OnSuccess tinyint, @OnFail tinyint;

DECLARE StepList CURSOR LOCAL FAST_FORWARD FOR
    SELECT s.step_id, s.step_name, s.database_name, s.command, s.on_success_action, s.on_fail_action
    FROM MsdbCopyDemo.dbo.sysjobsteps AS s
    JOIN MsdbCopyDemo.dbo.sysjobs AS j ON j.job_id = s.job_id
    WHERE j.name = N'DemoNightlyLoad'
    ORDER BY s.step_id;

OPEN StepList;
FETCH NEXT FROM StepList INTO @StepId, @StepName, @Db, @Command, @OnSuccess, @OnFail;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoNightlyLoad', @step_id = @StepId,
         @step_name = @StepName, @subsystem = N'TSQL', @database_name = @Db,
         @command = @Command, @on_success_action = @OnSuccess, @on_fail_action = @OnFail;
    FETCH NEXT FROM StepList INTO @StepId, @StepName, @Db, @Command, @OnSuccess, @OnFail;
END;
CLOSE StepList;

Next come the schedule and the target server. Without sp_add_jobserver the job belongs to no server and can never run.

DECLARE @Name sysname, @Type int, @Interval int, @StartTime int;

SELECT @Name = sch.name, @Type = sch.freq_type, @Interval = sch.freq_interval,
       @StartTime = sch.active_start_time
FROM MsdbCopyDemo.dbo.sysschedules AS sch
JOIN MsdbCopyDemo.dbo.sysjobschedules AS js ON js.schedule_id = sch.schedule_id
JOIN MsdbCopyDemo.dbo.sysjobs AS j ON j.job_id = js.job_id
WHERE j.name = N'DemoNightlyLoad';

EXEC msdb.dbo.sp_add_jobschedule @job_name = N'DemoNightlyLoad', @name = @Name,
     @freq_type = @Type, @freq_interval = @Interval, @active_start_time = @StartTime;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'DemoNightlyLoad', @server_name = N'(local)';

The job is disabled, with both steps, the same routing and the schedule. The new job has a new job ID, so the second query says different. Anything that pointed at the old ID needs a second look.

SELECT j.name, j.enabled, s.step_id, s.step_name, s.on_success_action, s.on_fail_action,
       sch.name AS schedule_name
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules AS sch ON sch.schedule_id = js.schedule_id
WHERE j.name = N'DemoNightlyLoad'
ORDER BY s.step_id;

SELECT live.enabled,
       CASE WHEN live.job_id = old.job_id THEN N'same' ELSE N'different' END AS job_id_vs_old
FROM msdb.dbo.sysjobs AS live
JOIN MsdbCopyDemo.dbo.sysjobs AS old ON old.name = live.name
WHERE live.name = N'DemoNightlyLoad';

Run the restored job

A recovered definition only counts if the job runs. Start it by hand. It is still disabled, so the schedule stays off, but sp_start_job starts it anyway. The loop polls the history for up to a minute.

DECLARE @JobId uniqueidentifier =
    (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DemoNightlyLoad');
DECLARE @Seconds int = 0;

EXEC msdb.dbo.sp_start_job @job_id = @JobId;
WHILE @Seconds < 60 AND NOT EXISTS
      (SELECT 1 FROM msdb.dbo.sysjobhistory WHERE job_id = @JobId AND step_id = 0)
BEGIN
    WAITFOR DELAY '00:00:01';
    SET @Seconds += 1;
END;

SELECT step_id, step_name, run_status,
       CASE WHEN step_id = 0 THEN LEFT(message, CHARINDEX(N'.', message))
            ELSE SUBSTRING(message, CHARINDEX(N'. ', message) + 2, 60) END AS message
FROM msdb.dbo.sysjobhistory
WHERE job_id = @JobId
ORDER BY instance_id;

Both steps ran in order, each with run_status 1 (succeeded). The last row, step_id 0, is the job outcome, and it succeeded too. The history holds only this run, because the new job ID has no past.

Enable the job only after you have compared everything. The last block removes the demo job, the copy and the backup file. SQL Server has no plain command to delete a file, so I use xp_delete_files.

EXEC msdb.dbo.sp_delete_job @job_name = N'DemoNightlyLoad';
DROP DATABASE IF EXISTS MsdbCopyDemo;

DECLARE @Backup nvarchar(400) =
    CONVERT(nvarchar(400), SERVERPROPERTY('InstanceDefaultBackupPath')) + N'\MsdbDemo.bak';
EXEC master.sys.xp_delete_files @Backup;

SELECT file_exists AS backup_file_left
FROM sys.dm_os_file_exists(@Backup);

Next time a job vanishes, grab the backup before you grab the panic button.

A recovered job is not a row in a copy, it is a job that has run.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.