Copy Jobs between servers

Question

Copy Jobs between servers

gfahrlander

SSCommitted

Points: 1888
More actions
June 23, 2003 at 9:53 am

#64232

What's the best way to copy vendor supplied jobs (month-end, etc.) from development server to production server?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Robert W Marda SSChampion Points: 13413 More actions · Answer 1

I believe the best way is to simply script the job and then execute that job on the production server. I don't know of any other way to move jobs.

Robert W. Marda

SQL Programmer

bigdough.com

The world’s leading capital markets contact database and software platform.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Robert W Marda SSChampion Points: 13413 More actions · Answer 2

I meant to say and then execute that script on the production server.

Robert W. Marda

SQL Programmer

bigdough.com

The world’s leading capital markets contact database and software platform.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 3

I agree. I would script the job onto development. If successful, then script to production. There are a number of stored procedures that must be used to create a complete job. Here is a sample script we recently used:



USE msdb

GO

EXEC dbo.sp_add_job 

  @job_name = N'UpdateSystemSettings'

, @enabled = 1

, @description = N'Updates the Earliest Completion Dates and System Lockdown.  Runs manually or automatically.'

, @owner_login_name = N'sa'

, @notify_level_eventlog = 2

GO



DECLARE 

  @InitialCurrentEarliestCompleteDate VARCHAR(16)

, @InitialNextEarliestCompleteDate VARCHAR(16)

, @InitialNextLockdownDateTimeVARCHAR(16)

, @InitialUSerCHAR(8)

, @sqlNVARCHAR(3200)



SET @InitialCurrentEarliestCompleteDate = '2003-05-30 00:00:00'

SET @InitialNextEarliestCompleteDate = '2003-05-31 00:00:00'

SET @InitialNextLockdownDateTime = '2003-06-02 12:00:00'

SET @InitialUser = 'SYSTEM'

SET @sql= N'EXEC upd_SystemSettings ''' + @InitialCurrentEarliestCompleteDate + ''' , '''

+ @InitialNextEarliestCompleteDate + ''' , '''

+ @initialNextLockdownDateTime + ''' , '''

+ @InitialUser + ''', 1'



PRINT @sql



EXEC dbo.sp_add_jobstep 

  @job_name = N'UpdateSystemSettings'

, @step_name = N'Update System Table and reschedule (recursive call to this job)'

, @command = @sql

, @database_name = N'dbPDS'

, @server = N''

, @subsystem = N'TSQL'

, @cmdexec_success_code = 0

, @flags = 0

, @retry_attempts = 0

, @retry_interval = 1

, @output_file_name = N''

, @on_success_step_id = 0

, @on_success_action = 1

, @on_fail_step_id = 0

, @on_fail_action = 2

GO

EXEC dbo.sp_update_job  

  @job_name= N'UpdateSystemSettings'

, @start_step_id = 1 

GO



EXEC dbo.sp_add_jobschedule 

  @job_name = N'UpdateSystemSettings'

, @name = N'UpdateSystemRecursive'

, @enabled = 1

, @freq_type = 1

, @active_start_date = 20030601

, @active_start_time = 120000

GO



EXEC dbo.sp_add_jobserver 

  @job_name = N'UpdateSystemSettings'

, @server_name = N'(local)' 

GO

neder SSC Eights! Points: 888 More actions · Answer 4

you can create a script of the existing job by right clicking on the job, then 'All Tasks' the Generate SQL Scripts.

mccork SSC-Insane Points: 22288 More actions · Answer 5

If there's a lot of jobs to be scripted, right-click on JOBS in the EM tree and then 'All Tasks' then Generate SQL Scripts. You can manually edit out unwanted jobs from the script before executing it.

Note: I also uncheck "Replace job if it exists" in most cases. This saves my hide if I were to accidentally run the script on the wrong server (not that I ever would though )

Unfortunately EM doesn't provide for generating a script for multiple jobs selected in the job list pane.

Cheers,
- Mark