April 25, 2008 at 10:13 am
Gurus,
I am new to this forum. I need a help. I want to transfer all the SQL Agent job to another server.
I know i can do this thru SSIS But , I wanted to do with Stored procedures.
Any help wolud be much appriciated
Thank you
kumar
April 25, 2008 at 12:07 pm
The easiest way to copy jobs is to script them as CREATE JOB statements on the source instance and run the scripts on the destination instance. You can click on "Jobs" in SSMS, highlight all the jobs in the summary list, right-click, and script them all. If you don't see the Summary list, press F7.
Greg
April 25, 2008 at 12:13 pm
Thanks for the reply!!
I agree with you. In my case daily my office people will add new jobs, then daily i have to look the new jobs and i have to transfer the jobs. Instead of doing this i want write a store procedure, While first execution it should transfer all the jobs to target server and second time the newly created jobs. So I am expecting solution for this.
I am writting the SP for the last twodays, but i couldnt get the exact result. 🙁
April 28, 2008 at 9:58 am
In that case, I suggest you set the parameters for sp_add_job, sp_add_jobschedule, and sp_add_jobstep with data selected from msdb.dbo.sysjobs, msdb.dbo.sysjobschedules, and msdb.dbo.sysjobsteps. If you run it every day, you could check the date_created column in sysjobs so you only select jobs created that day.
When you've set the parameters, you can sp_add_job, sp_add_jobschedule, and sp_add_jobstep in the other instance.
Is that anything like what you've tried yet?
Greg
April 28, 2008 at 3:42 pm
Greg,
Thanks for the reply. let me try this way. it seems to be a good solution for me. I will let u know the status with in 2 days.
Thanks once again
-- Krishna
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply