How to rebuild MS Agent Jobs from MSDB table?

Question

This morning our DB server died.

They restored backups to another server but these didn't include the many MS Agent jobs.

Anyway I have managed to get a backup of the old MSDB table where all the tables used to create MS Agent Jobs are held added to our new server.

Therefore I need a script to re-create them on the new server.

There must be a job somewhere in MS SQL to script them out as you can do it from the management console. Therefore does anyone know of a script to do this or where to find the MS one please let me know.

As no manual backups were created a lot of jobs will be missing and people won't know what to do to re-create them manually (which is why I think being able to add them to the nightly backup process would be good - I'm a webdev so it's not my job - I just happen to be the only person around to do this lovely task).

Any help would be much appreciated.

Solution

There are solutions to this problem all over the forum and blog sites @MonkeyMagix.

What you need to do is restore the msdb backup as msdb_old (or something similar), then run code against the _old DB to

1) build the code that creates the Agent jobs from the _old DB;

http://www.sqlservercentral.com/scripts/generate+jobs+scipts/68105/

or

2) insert the Agent jobs directly into the new tables. http://annevamsikrishna.blogspot.com/2012/02/recovering-sql-agent-jobs.html

Recovering a specific job definition from an MSDB backup?

Comments

[squillman]

Have a look here: serverfault.com/a/34094/1054

[MonkeyMagix]

Good - but read my comments about the 2 errors I was getting. Thanks though.

[Mark Allison]

Do you have a recent backup of msdb?