Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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
share|improve this answer
    
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

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!

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.