September 5, 2008 at 8:52 am
I"ve googled this and it appears this is doable using SSMS in S2K5 but how do I do this in S2K sp4? I brought over two DTS jobs that I want to run/modify but I cannot figure out how to move the SQL agent jobs that run the DTS packages, as well as another 30+ steps. I scripted the jobs to a file but it appears it wants to simply run each step and references msdb for specifics. I don't want to migrate msdb and overlay everything else that is setup on the dev server. Anyone have a method for copying the jobs from one server to another? Or am I simply missing the obvious?
-- You can't be late until you show up.
September 5, 2008 at 9:04 am
I'm not sure what you mean when you say, "it wants to simply run each step...". When I script a job, whether it's on a SQL 2000 instance or a SQL 2005 instance, the script includes msdb.dbo.sp_add_category, msdb.dbo.sp_add_job_step, msdb.dbo.sp_add_jobschedule, msdb.dbo.sp_update_job, and msdb.dbo.sp_add_jobserver. Those are the stored procedures that are run by SSMS or Enterprise Manager when the GUI is used to create a job.
Greg
September 5, 2008 at 10:55 am
Upon closer inspection, it appears it does want to ADD the steps, not execute them. I'm very skittish with these two jobs because there are references to linked-production servers within the steps and I sure don't want to screw something up that's live, hence my desire to have a true test environment (which they've never had here, if you can believe that!). So, to be absolutely clear, by executing the script that I generated on the prod server onto the dev server, it'll create my jobs with the proper steps, not actually execute any of the steps?? Paranoia has set in!
-- You can't be late until you show up.
September 5, 2008 at 11:58 am
Yeah, it'll just create the jobs. We've used these scripts for years to migrate jobs to new instances and never had anything bad happen. You may want to regenerate the DTSRUN command on the dev server to make sure it references the package correctly. I like to use the dtsrunui utility to generate the DTSRUN command.
Good Luck!
Greg
September 5, 2008 at 12:31 pm
Thanks. I've already run one of the scripts and it created the job fine. Again, paranoia on my part - I think because it seemd too simple (good ole' MS!). Some minor things that needed to be cleaned up - i.e. an operator didn't exist in dev, a database didn't exist, etc. - but again, minor. Thanks for alleviating my fears.
-- You can't be late until you show up.
September 8, 2008 at 10:16 am
No problem. I know what it's like to be not quite sure if I should try something!
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply