Hi

Well, I would I have restored the whole msdb except I am not "in charge" of the DBS and the BODS who are seem to know less about SQL Servers than than webdevs like me it seems.

Also because of the existing MS Agent jobs in the new DB Server I couldn't just over-rule them (I was working a 35hour shift from my bedroom here!)

The script DID work apart from the FK problems at the bottom of the script. I did try numerous attempts at fixes but couldn't get them to work for some reason. So I just went into each job after and re-set the schedule which I had to do anyway for the Target option.

A search from the new table to old one to find jobs NOT IN the new MSDB helped me find which jobs to run then I just ran then one by one. set up the schedule and turned on the Target Server spec.

However seeing the script worked so well I am just wondering how I can fix those last two problems so that it could be used to

-rebuild jobs that have been accidentally deleted

-copy jobs from one sys to another by script

-and of course restore from backup MISSING jobs

The thing is that the old DB was MS 2005 the new MS 2012 so I am guessing the fact there were lots of jobs already IN there was because they were SYSTEM jobs?

So overwriting the MSDB table would have caused more problems wouldn't it?

Being able to put this in a loop with error handling and checks etc would be good to have seeing that no-one seems to backup MS-AGENT jobs so does anyone know how to turn the Target Local Server option on with SQL AND handle the FK issue?

I am guessing the Target feature was added after the script was made, maybe a 2008-2012 feature?

Thanks for your help etc - trust me if I could have overwritten the whole MSDB table without ringing up the server bod at 3 am I would have tried!