0

I've got a job in SQL Server Management Studio and I want to back up the schedule that it runs on so that the schedule can be applied to other jobs that I add. I know that I can get what I assume is the data I need to copy from using the following:

-- lists all aspects of the information for the job NightlyBackups.
USE msdb ;
GO

EXEC dbo.sp_help_job
    @job_name = N'NightlyBackups',
    @job_aspect = N'SCHEDULES' ;
GO

I'm just wondering how I can store the results of this stored procedure in a way that will allow me to add it to other jobs on the system. Preferably in T-SQL .

Corey Maddie Besmer
  • 23
  • 1
  • 6

1 Answers1

0

The GUI method: Right-click the job in SSMS and script it as CREATE; alter parameters to suit.

The T-SQL method: I don't have that on-hand, but try opening Profiler, look for SQL:Completed and RPC:Completed, and then do the GUI method - you should capture the T-SQL that SSMS is executing! Alter to suit.

Anti-weakpasswords
  • 2,604
  • 20
  • 25
  • Well, I _have_ the SQL that the job executes, I'm wondering how I can save the results of this. The `sp_help_job` stored procedure does a bunch of table joining to produce a table that contains all of the info. What I'm wondering is, how can I store this table so that I can then use `sp_update_job` to add the info to a different job in the future? – Corey Maddie Besmer Mar 18 '14 at 13:24