question

balanarasimhac-1495 avatar image
0 Votes"
balanarasimhac-1495 asked Yufeishao-msft commented

how to generate script for all enabled jobs

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

Thanks in advance

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please do not forget to mark as accepted answer wherever information provided to you were helpful.

Regards

0 Votes 0 ·
balanarasimhac-1495 avatar image
0 Votes"
balanarasimhac-1495 answered pituach commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


You've Transfer Jobs Task and Transfer Logins Task available in SSIS for this purpose - this is probably your one of your best options. You can use filter using the query above to transfer only enabled jobs

140130-image.png


0 Votes 0 ·
image.png (2.1 KiB)
pituach avatar image
0 Votes"
pituach answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft commented

Hi @balanarasimhac-1495

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is true 141015-image.png but the request was to generate script for all enabled agent jobs and manually choosing only the enabled jobs out of tens of jobs, can be a bit time consuming, while script or SSIS (once you know how) can solve the issue much faster probably.



0 Votes 0 ·
image.png (727 B)

Hi @balanarasimhac-1495
If the reply above was helpful please mark as accepted answer so it can be helpful for other community members with same questions.

0 Votes 0 ·