MSDB Backup – Recovering a Specific Job Definition from an MSDB Backup

sql serversql-server-2005

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?

Best Answer

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
Related Topic