Recovering a specific job definition from an MSDB backup?
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
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
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!