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?

Answer 1

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

Answer 2

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!

Answer 3

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