Take the 2-minute tour ×
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.

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]
GO

/****** Object:  Job [eLFCopy]    Script Date: 12/19/2013 11:30:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
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)
BEGIN
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

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'eLFCopy', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @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', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @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', 
        @database_name=N'master', 
        @flags=0
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', 
        @enabled=1, 
        @freq_type=8, 
        @freq_interval=2, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20110701, 
        @active_end_date=99991231, 
        @active_start_time=100, 
        @active_end_time=235959, 
        @schedule_uid=N'c406cae9-49b7-40dd-9438-cef44160f562'
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
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
share|improve this question
    
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 '13 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. –  Shayma Ahmad Dec 19 '13 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 '13 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 –  Shayma Ahmad Dec 19 '13 at 20:27
add comment

2 Answers

up vote 4 down vote accepted

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', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20080101, 
        @active_end_date=99991231, 
        @active_start_time=20000, 
        @active_end_time=235959, 
        @schedule_uid=N'3706c58c-cad3-481f-a565-2e1aed81ee8e'
   ---------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

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.

share|improve this answer
1  
Thanks again for your replies, its so confusing as there are job_id, schedule_id or schedule_uid, step_id...subplans.. –  Shayma Ahmad Dec 19 '13 at 19:53
add comment

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.

EDIT: Removed request to move to comment after Aaron's Comment

share|improve this answer
    
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. –  Aaron Bertrand Dec 19 '13 at 19:19
    
Thanks @capnhector , it seems I have to create one manually to understand how these jobs work. –  Shayma Ahmad Dec 19 '13 at 19:54
add comment

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.