how to generate script for all enabled jobs

Question

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

Thanks in advance

Answer 1

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.

Answer 2

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".

Answer 3

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

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

Answer 4

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