5

I had a MSSQL (MSSQL 2008 R2) cluster that failed in a way that I couldn't bring it back up (long story, servers ended up in BSOD loops, I'm blaming Broadcom and myself).

I've SQL backups, and even better, I've got the detached MDF and LDF files as they were on the SAN before everything went wrong. I've now got the task of restoring these back to a newly built server.

The databases themselves, logins and SSIS packages aren't a problem, as I've got all these. The thing that's bothering me is the SQL Agent jobs that were defined on the previous server. I'm dimly aware that these are stored in MSDB somewhere, but what steps should I go through to retreive these from the MDF/LDF files I've already got and load them on the new server?

I'm not going to be restoring the modal, msdb, master and temp databases on the new server, so should I be looking to attach the old files under different names and pull job definitions out of them, or is there some other (better) way?

Improve this question
1

2 Answers 2

Reset to default
8

You will have to attach the MSDB DB under a different name like 'jobsdb' and pull the jobs out of jobsdb. The jobs will be stored under jobsdb.dbo.sysjobs and the job steps will be under jobsdb.dbo.sysjobsteps. They can be joined by the job_id column.

The schedules can be found under jobsdb.dbo.sysjobschedules joined with jobsdb.dbo.sysschedules on schedule_id.

The rows can be added into your current msdb using a insert-select, or by using the stored procedures: sp_add_job, sp_add_jobstep, sp_add_jobschedule, sp_add_jobserver.

Improve this answer
1
  • In @MarkStorey-Smith's solution you could run into issues with the ID's/Guids in auto-generation scripts - even comparing all the jobs to see what (if anything) changed would take way too much effort. It is far better/easier/faster to restore to a temporary database and run a query to list all the Job Details. You could even write a script with cross-database joins to msdb to truly see which Job/Step/Schedule changed across all of your Jobs. Best to prepare your queries now, so when the time comes (hair on fire) you will be ready to troubleshoot from a backup in just a few minutes.
    – MikeTeeVee
    Aug 16, 2019 at 9:18
3

Assuming service has been restored and you're now attending to this without urgency, I'd be inclined to restore msdb to a separate instance if available. You are correct that the agent job definitions are stored in msdb, so following restore you can script the jobs you want to recover and re-create on the primary server.

Whether restoring to the primary server or a temporary instance, the SQL Server build version must match that of the backup.

Check the target instance with:

SELECT @@version
-- or
SELECT SERVERPROPERTY('ProductVersion')

Check the backup with:

RESTORE HEADERONLY
FROM DISK = '<path>'

The resultset will contain SoftwareVersionMajor, SoftwareVersionMinor and SoftwareVersionBuild columns that must match the major.minor.build string returned by the target instance.

It should also be possible to attach the msdb data files as described in the answers to Restore System Databases from MDF/LOG files.

Improve this answer
4
  • Just to clarify, do you mean restore msdb as msdb? Or restore it under some other temporary name, and pull the jobs manually out of the tables?
    – growse
    Feb 12, 2012 at 0:51
  • I was referring to restoring as msdb. That said, there is nothing to stop you restoring the backup as msdbRecovered and retrieving the jobs manually.
    – Mark Storey-Smith
    Feb 12, 2012 at 1:07
  • Thanks for the clarification. I guess I'm just concerned that restoring as msdb might mess some other bits and pieces up on the new server. I'll read through your other answers to get a bit more insight.
    – growse
    Feb 12, 2012 at 10:56
  • @growse I would be too (probably unnecessarily), which is why given the choice I'd restore to a different instance and script the jobs out.
    – Mark Storey-Smith
    Feb 12, 2012 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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