Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
We're not huge fans of Broadcom here. Would appreciate hearing your experiences if you can pop into chat sometime. –  Mark Storey-Smith Feb 11 '12 at 23:55
add comment

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
This is useful stuff, thanks. –  growse Feb 12 '12 at 10:56
add comment

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.

share|improve this answer
    
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 '12 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 '12 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 '12 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 '12 at 19:57
add comment

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.