Recovering a specific job definition from an MSDB backup?

Question

We've been asked to recover a specific SQL 2005 job from how it existed at a certain time in the past. We have MSDB database backups from that time period, but I can't overwrite the current MSDB database, because I don't want to lose more recent changes to other jobs on that server.

If I restore it as a copy (call it MSDB_old or something), how can I extract the information for that one job? Or would I need to restore it over the MSDB database on a scratch server?

squillman · Accepted Answer · 2009-06-30T21:05:34.290

No need to restore to a scratch server, you can restore it to something like you say (MSDB_old) and do a query to get your job back:

USE msdb_old
SELECT * FROM sysjobs
JOIN sysjobsteps ON sysjobs.job_id=sysjobsteps.job_id
WHERE sysjobs.NAME='My Lost Job'
ORDER BY sysjobsteps.step_id

You'll have to restore

the entry in sysjobs
each entry in sysjobsteps related to the above entry in sysjobs
entries in sysjobhistory if you want history back
entries in sysjobschedules to get your schedules back

EDIT: Here's a script that should do it in SQL 2005 and 2008 (assuming your job was called "My Lost Job" and you restored to MSDB_Old)

DECLARE @JobID UNIQUEIDENTIFIER
SELECT @JobID = job_id FROM msdb_old.dbo.sysjobs WHERE NAME='My Lost Job'

INSERT msdb.dbo.sysjobs
SELECT * FROM msdb_old.dbo.sysjobs
WHERE job_id=@JobID

INSERT msdb.dbo.sysjobsteps
SELECT * FROM msdb_old.dbo.sysjobsteps
WHERE job_id=@JobID

SET IDENTITY_INSERT msdb.dbo.sysjobhistory ON
INSERT msdb.dbo.sysjobhistory
    (instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
     [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
     operator_id_netsent,operator_id_paged,retries_attempted,[server])
SELECT
    instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
    [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
    operator_id_netsent,operator_id_paged,retries_attempted,[server]
FROM msdb_old.dbo.sysjobhistory
WHERE job_id=@JobID
SET IDENTITY_INSERT msdb.dbo.sysjobhistory OFF

INSERT msdb.dbo.sysjobschedules
SELECT * FROM msdb_old.dbo.sysjobschedules
WHERE job_id=@JobID

Thanks for the script, it saved us from a corrupted msdb! The sysjobschedules part fails with a foreign key error, though, we add the recreate the schedules manually. — Guillaume Filion, Dec 19 '12 at 18:00
We also add to run EXEC dbo.sp_add_jobserver @job_name = N'NightlyBackups' to register them with the server. — Guillaume Filion, Dec 19 '12 at 18:50

score 3 · Answer 2 · edited May 06 '15 at 13:33

The script is loosely based on this one in the accepted answer. It has been updated for SQL 2014, with exception handling, atomic transactions, and a few other improvements.

-- Script for SQL 2014
DECLARE @JobID UNIQUEIDENTIFIER
declare @servername sysname

set @servername = @@SERVERNAME

SELECT @JobID = job_id 
FROM msdb_old.dbo.sysjobs 
WHERE name='My Lost Job'

BEGIN TRAN

BEGIN TRY

INSERT msdb.dbo.sysjobs
SELECT * FROM msdb_old.dbo.sysjobs
WHERE job_id=@JobID

INSERT msdb.dbo.sysjobsteps
SELECT * FROM msdb_old.dbo.sysjobsteps
WHERE job_id=@JobID

SET IDENTITY_INSERT msdb.dbo.sysjobhistory ON
INSERT msdb.dbo.sysjobhistory
 (instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
 [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
 operator_id_netsent,operator_id_paged,retries_attempted,[server])
SELECT
 instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
 [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
 operator_id_netsent,operator_id_paged,retries_attempted,[server]
FROM msdb_old.dbo.sysjobhistory
WHERE job_id=@JobID
SET IDENTITY_INSERT msdb.dbo.sysjobhistory OFF

-- New insert in sysschedules 
SET IDENTITY_INSERT msdb.dbo.sysschedules ON
INSERT msdb.dbo.sysschedules (schedule_id, schedule_uid,
         originating_server_id, name, owner_sid, enabled,
         freq_type,freq_interval, freq_subday_type,
         freq_subday_interval, freq_relative_interval,
         freq_recurrence_factor, active_start_date, 
         active_end_date, active_start_time, active_end_time,
         date_created, date_modified, version_number)
SELECT schedule_id, schedule_uid, originating_server_id, name,
       owner_sid, enabled, freq_type, freq_interval, freq_subday_type,
       freq_subday_interval, freq_relative_interval,
       freq_recurrence_factor, active_start_date, active_end_date,
       active_start_time, active_end_time, date_created, date_modified,
       version_number 
FROM msdb_old.dbo.sysschedules a
WHERE schedule_id = (select schedule_id from msdb_old.dbo.sysjobschedules b where job_id=@JobID )
SET IDENTITY_INSERT msdb.dbo.sysschedules OFF

INSERT msdb.dbo.sysjobschedules
SELECT * FROM msdb_old.dbo.sysjobschedules
WHERE job_id=@JobID

-- Alter job as local job
EXEC msdb.dbo.sp_add_jobserver @job_id=@JobID, @server_name = @servername

END TRY
BEGIN CATCH
SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;

    ROLLBACK TRAN

    RETURN
END CATCH

COMMIT TRAN

@DeerHunter: If this is an improvement working on MSSQL2014 only, it should not be edited back into the answer, as this was concerned with a much older version. — Sven, May 06 '15 at 13:17
Just to point out, if any jobs had categories set, those categories would have to be imported as well (or update category_id to 0 in table). Otherwise those jobs wouldn't be displayed in the job list in the object explorer. — Rivka, Jul 01 '15 at 14:54

score 1 · Answer 3 · answered Jun 30 '09 at 20:43

The easiest way to extract a single job from MSDB is to right-click the job in SSMS and say Script Job - then take the script to the target server and run it to recreate the job (with potentially some modifications needed).

This only works if the msdb is restored as msdb - which means in your case you'd have to restore the backup as msdb on a scratch server.

I guess you could restore it as a copy of msdb and then manually pull everything out of the various msdb_copy.dbo.sysjobs/sysjobsteps/sysjobschedules/sysjobservers tables using a join.

Hope this helps!

score 1 · Answer 4 · answered Sep 11 '15 at 17:17

Hi, I'd like to add upon squillman's answer. I tested it in 2008 R2.

First, by fixing the error with FK violation with sysjobschedules and sysschedules.

Secondly, by running dbo.sp_add_jobserver.

Lastly, by having it loop through all the jobs in msdb_old.dbo.sysjobs for a complete replication.

DECLARE @JobID UNIQUEIDENTIFIER
declare @jobname nvarchar(128)

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT DISTINCT job_id 
FROM msdb_old.dbo.sysjobs

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @JobID
WHILE @@FETCH_STATUS = 0

BEGIN
SELECT @jobname = name FROM msdb_old.dbo.sysjobs WHERE job_id=@jobid
print @jobname

print 'insert in sysjobs'
INSERT msdb.dbo.sysjobs
SELECT * FROM msdb_old.dbo.sysjobs
WHERE job_id=@JobID

print 'insert in sysjobsteps'
INSERT msdb.dbo.sysjobsteps
SELECT * FROM msdb_old.dbo.sysjobsteps
WHERE job_id=@JobID

print 'insert in sysjobhistory'
SET IDENTITY_INSERT msdb.dbo.sysjobhistory ON
INSERT msdb.dbo.sysjobhistory
    (instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
     [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
     operator_id_netsent,operator_id_paged,retries_attempted,[server])
SELECT
    instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
    [message],run_status,run_date,run_time,run_duration,operator_id_emailed,
    operator_id_netsent,operator_id_paged,retries_attempted,[server]
FROM msdb_old.dbo.sysjobhistory
WHERE job_id=@JobID
SET IDENTITY_INSERT msdb.dbo.sysjobhistory OFF

print 'insert in sysschedules'
SET IDENTITY_INSERT msdb.dbo.sysschedules ON
INSERT msdb.dbo.sysschedules
( [schedule_id]
,[schedule_uid]
,[originating_server_id]
,[name]
,[owner_sid]
,[enabled]
,[freq_type]
,[freq_interval]
,[freq_subday_type]
,[freq_subday_interval]
,[freq_relative_interval]
,[freq_recurrence_factor]
,[active_start_date]
,[active_end_date]
,[active_start_time]
,[active_end_time]
,[date_created]
,[date_modified]
,[version_number])
SELECT  s.[schedule_id]
,s.[schedule_uid]
,s.[originating_server_id]
,s.[name]
,s.[owner_sid]
,s.[enabled]
,s.[freq_type]
,s.[freq_interval]
,s.[freq_subday_type]
,s.[freq_subday_interval]
,s.[freq_relative_interval]
,s.[freq_recurrence_factor]
,s.[active_start_date]
,s.[active_end_date]
,s.[active_start_time]
,s.[active_end_time]
,s.[date_created]
,s.[date_modified]
,s.[version_number]
FROM msdb_old.dbo.sysschedules s, msdb_old.dbo.sysjobschedules j
WHERE j.job_id=@JobID and s.schedule_id = j.schedule_id
SET IDENTITY_INSERT msdb.dbo.sysschedules OFF

print 'insert in sysjobschedules'
INSERT msdb.dbo.sysjobschedules
SELECT * FROM msdb_old.dbo.sysjobschedules
WHERE job_id=@JobID

print'exec adds job in server'
use msdb
EXEC dbo.sp_add_jobserver @job_id = @jobid

FETCH NEXT FROM MY_CURSOR INTO @JobID
END

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

score 0 · Answer 5 · answered Jun 30 '09 at 20:43

0

just restore to any server with a new name, I have done this many times before...

answered Jun 30 '09 at 20:43

SQLGuyChuck

114
5

Recovering a specific job definition from an MSDB backup?

5 Answers5

Linked