how to generate script for all enabled jobs

Bala Narasimha Challa 466 Reputation points
2021-10-13T08:42:02.507+00:00

Hi Team,
how to generate script for all enabled agent jobs (not all jobs).

Thanks in advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,710 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Bala Narasimha Challa 466 Reputation points
    2021-10-13T09:29:33.763+00:00

    Thanks for quick update.

    Have 150 jobs in Server1 and few are enabled & few are disabled.

    I want to migrate enabled jobs to Server2 from Server1.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2021-10-13T08:53:26.787+00:00

    Good day,

    If you can elaborate a bit more on what script you want to execute then we might be able to give a more focus solution. In general you can find all the jobs which are enabled by query the msdb.dbo.sysjobs table. I am not sure what you mean by generate script on a job but if you mean like change configure the job or start it then you can loop on the enabled job and execute your script or use JOIN/APPLY if fit

    To find all the jobs enabled use the following: 

    SELECT job_id, notify_level_email, name, enabled, description
FROM msdb.dbo.sysjobs
where enabled = 1
    0 comments
  2. Olaf Helper 40,816 Reputation points
    2021-10-13T11:33:03.257+00:00

    Use PowerShell DbaTools CmdLet with function Copy-DbaAgentJob together with the job list from Ronen script

    https://docs.dbatools.io/#Copy-DbaAgentJob

    0 comments
  3. YufeiShao-msft 7,056 Reputation points
    2021-10-14T07:19:50.257+00:00

    Hi @Bala Narasimha Challa ，

    you can do it manually with SSMS:
    expand SQL Server Agent, expand Jobs, right-click the job you want to script
    from the right-click menu, select Script Job as, then CREATE TO or DROP TO and click one of the following:
    New Query Editor Window, which opens a new Query Editor window and writes the T-SQL script to it
    File, which saves the T-SQL script to a file
    Clipboard, which saves the T-SQL script to the Clipboard

    you can also refer to this:
    https://stackoverflow.com/questions/3361163/automatically-create-scripts-for-all-sql-server-jobs

    ---------------------------------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".