I have a SQL Server 2005 job in the SQL-Server Agent Jobs that I need to move/transfer to another updated SQL Server 2008 R2 instance. I was able to script it out and then use it as a new query window in the upgraded dev. server, It seems(as far as I know) that the only changes that I have to make to this script is to point it to the correct server, file path and get it a new @schedule_uid number but not sure how to assign that number.

  • This job was built for an SSIS package (as its pointing to in the file path)

Thanks for any advice

Script code is:

USE [msdb]

/****** Object:  Job [eLFCopy]    Script Date: 12/19/2013 11:30:57 ******/
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/19/2013 11:30:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'eLFCopy', 
        @description=N'This job copies eLF files & folders from stage locations to active directories.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'BIO\WDDocManagement', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RunPackage]    Script Date: 12/19/2013 11:30:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RunPackage', 
        @os_run_priority=0, @subsystem=N'SSIS', 
        @command=N'/FILE "C:\SSIS\Packages\elfcopy.dtsx" /CONNECTION "BRORPORAP1.WDDocumentManagement";"Data Source=BRORPORAP1;Initial Catalog=WDDocumentManagement;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /CONNECTION RCSMTP;"SmtpServer=brutmurex3.bio.ri.redcross.net;UseWindowsAuthentication=True;EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'WeeklySchedule', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave

  • That "number" is the related schedule. You need to create one with the same interval if it does not exist or reuse the existing
    – billinkc
    Dec 19, 2013 at 18:53
  • Thank you guys for the quick reply, I actually used the same schedule_uid number and it seems its available, I executed successfully. I'm not sure how can I share all the code here as I still don't see the SSIS package in the SSIS as the file path in the script point to it and I manually copied and pasted it from the C:drive in the first server to the C: drive for the new server... should I import the package manually? by exporting it from the SSIS \ StoredPackages...? Thanks again. Dec 19, 2013 at 19:29
  • Yes, you need to copy/export the packages from the old server to the new server (or you must modify your job to reference the packages on the old server)
    – billinkc
    Dec 19, 2013 at 19:32
  • @billinkc the job itself that I just re-created in the new server already reference the package path if you can see my script code above, which is in the C: drive so what do you mean by modify? as I copied and pasted the .dtsx file in the new C: drive for the new server too. I finally was able to import the old package by right click in the SSIS/MSDB folder in the Integration Services, and used the file system, location for C:...dtsx, but its pointing still to the old server, how can I edit it to match the Job modifications I made (which is just to point it to AP3 server instead of AP1)thanx Dec 19, 2013 at 20:27

2 Answers 2


If you script a job, it will script a schedule, including a GUID that should be created just fine if it doesn't yet exist on the new server. E.g.:

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
        @job_id=@jobId, @name=N'syspolicy_purge_history_schedule', 

It's ok to use this number on the new server, as long as you haven't already migrated another job that used the same schedule.

If you have, and you get errors, the whole thing will be rolled back. In this case you really should comment out this section and just manually assign the same schedule to this job. If you're really lazy, though, you could just change a couple of characters in the GUID.


The other option to Aaron's excellent advice is to just remove @schedule_uid, the procedure will create a new UID for the schedule. That is how I moved all the jobs from our old server to our new one with out issue.

  • 3
    I think that is fine as an answer, I completely forgot that you could just leave that out altogether and one would be generated for you. Dec 19, 2013 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.