3

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.

peterh
  • 4,914
  • 13
  • 29
  • 44
MonkeyMagix
  • 131
  • 1
  • 3

1 Answers1

3

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?

Community
  • 1
RyanB
  • 158
  • 1
  • 9