Tell me more ×
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.
up vote 2 down vote favorite

I tried to copy some SQL Agent Jobs from server1 to server2 by selecting rows from server1.msdb.dbo.sysjobs and inserting to server2.msdb.dbo.sysjobs. I did the same with sysjobsteps.

This seemed to work, and I verified that the records are in the new msdb tables.

Once I restart SQL Agent, I only see some of those jobs listed under SQL Server Agent in SSMS. I am very puzzled why SOME are not listed.

I am using SQL Server 2008 R2.

share|improve this question

1 Answer

up vote 1 down vote

A better option might be to script the jobs out instead of taking the information directly out of the system tables. In SSMS just right click each job and it should give you the option to script to a new query window or file.

Scripting the jobs out in this manner is probably the most affective, short of just restoring the msdb database to the other instance. You can review the script before running it on your second server and ensure the job steps and schedule looks right. If you have any that run frequently you might adjust the script to have those created as disabled, so you can verify first.

There are a few things that might affect why your pocess did not work, one being the order in which you inserted them and ensuring that the records or linked by the jobid like they should be.

share|improve this answer
+1 Definitely the way to go. – Thomas Stringer Sep 21 '12 at 17:25
thanks for the feedback. scripting would be an option if you only have handful of jobs to migrate. it becomes a nightmare if you have hundreds. still puzzled. job_id are kept intact for both sysjobs and sysjobsteps tables. that should be all it takes for sql agent to recognize all jobs. – Joy Walker Sep 21 '12 at 17:30
@JoyWalker Did you ever consider using an SSIS package to transfer the jobs? That would most likely be the easiest. – Thomas Stringer Sep 21 '12 at 17:32
Ah YES. I got it. each row in sysjobs has a category_id field. some of my imported rows have a non-existing category_id in the new system. Once I updated those category_id to a valid category_id on the new server. they all showed up nicely. Like Shawn mentioned, make sure related/linked ids are valid in the new systems. I am glad this method worked for me and hope this help someone. It can be a great time saver. – Joy Walker Sep 21 '12 at 17:54
Thanks Shark. Yes, I guess SSIS is another option too. – Joy Walker Sep 21 '12 at 17:55

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.