Copying Jobs and DTS to another server

[NeeNee]

I need to copy a series of jobs to our new production server. My DBA is creating the scripts to move the databases.

I am new at SQL Server and could use help possible.

 

[ClaireHCM]

For jobs
Take backup of msdb from one server to another server.
For DTS
copy the sysdtspackages table from one server and store in the new server

 

[Corran007]

I would be warry of coping the msdb if it already has things on it. If he does just copy the msdb, he already has the dts packages since they are stored in it.

The easiest thing is to select the jobs you want right click and select the generate sql script. that will get them into a script that you can run on the new server.

for dts packages, you can open the package and select save as and select the new server. This is the safest way to get them over.

You can try the copy msdb, but be careful. its possible to severly wreck your server if you are not careful

 

[NeeNee]

Thanks Corran007,

I did exactly as you suggested and it took me minutes to copy them all.

NeeNee

 

[JayKusch]

If you have alot of jobs to script out ... here is a job I have that will script out ALL jobs in on swoop ...


BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Script All Jobs')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Script All Jobs'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Script All Jobs'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Script All Jobs', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Script Jobs Step', @command = N'

Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8

Set conServer = CreateObject(&quot;SQLDMO.SQLServer&quot
conServer.LoginSecure = True
conServer.Connect &quot;Stage_DB&quot;

strFilename = &quot;C:\JOBS.sql&quot;

For Each oJB In conServer.JobServer.Jobs
strJob = strJob & &quot;--------------------------------------------------&quot; & vbCrLf
strJob = strJob & &quot;-- SCRIPTING JOB: &quot; & oJB.Name & vbCrLf
strJob = strJob & &quot;--------------------------------------------------&quot; & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing

Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing


', @database_name = N'VBScript', @server = N'', @database_user_name = N'', @subsystem = N'ActiveScripting', @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
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Enjoy!

Thanks

J. Kusch