SQL Server Performance Forum – Threads Archive

Transferring Sql Agent Jobs

I am trying to transfer MSSQL Server Agent jobs from a production machine to a test and a dev machine. The orignators of the procedures are not being able to help by the company that acquired them (read [<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

‘ />)]). So I am trying to find a way to transfer the jobs over. I figure there is a file that the Server Agent references outside of the databases themselves, but I can’t figure out what it is. Can anyone help me with this FAST? Thanks Bob

ALl jobs you create are in database msdb. There are 2 ways for you to transfer those jobs: from EM script those jobs than run them on the test server. Or you can backup msdb database and restore it on the test machine. Take into consideration that with the second approach you will lose whatever job or DTS packages you have on the destination server. If there are none, than it’s the fastes way to do it.
If server names are different, you will have to run an update on server name UPDATE msdb.dbo.sysjobs
SET originating_server = source_server_name
WHERE originating_server = destination_server_name
Bambola.

Right bambola. If the machines are connected you could also try using DTS and running the "Transfer Jobs Task". Nathan H.O.

"Transfer Jobs Task"? Where do I find that? Bambola.

Thats on SQL 2K : Transfer Jobs Task
Use this task to copy jobs from an instance of SQL Server 7.0 or SQL Server 2000 to an instance of SQL Server 2000. When configuring this task, specify the source and destination servers and the jobs to be copied. _________
Satya SKJ

I must be blind or something…. can’t find it 🙁

In the DTS Package Designer, Open the TASK menu. Click the Transfer Jobs Task (number 15 on the list) The little help there is for this stuff is under the "Transfer Database Objects Tasks" topic in BOL.
Nathan H.O.

Thanks! Lately we re-installed one of our dev SQL server. We backuped the msdb database, but there was no way to restore it. It gave a version error. In the begining I thought it was due to different service packs, but no. Later I’ve found out that old SQL Server was installed in Italian while the new one (and all the rest of our SQL server) are in english (as they should be! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Though I have a feeling it will not work (and I must say I did not lose anything important and I do have the job scripts), I will try to do it with "Transfer jobs task" Bambola.

"Operazione Di Lavori Di Trasferimento". I have a feeling it will work, just try it. Nathan H.O.

ahahahahaha! you’re funny Nathan!
that’s like translating "impostazioni" (setting in Italian) to "Impostations"! Bambola.

Nobody ever said I couldn’t read the BOL in Italian! I am willing to learn though. By the way did it (the job transfer) work? Nathan H.O.

I haven’t done it yet. I have too much to do and since there was nothing terrible important there, it has to wait. I will have to first install SQL server in italian on some machine than restore msdb there and only then I can try it. I’ll let you know the results. Grazie!

]]>