Share via

Using TSQL to Script Out SQL Server Agent Jobs into One File

LauraC 21 Reputation points
Sep 18, 2022, 10:52 PM

My Manager wants an Ansible Job created that will script out the SQL Server Agent Jobs into a text file as XXX.sql and then he wants an Ansible Job to execute the XXX.sql script to create the jobs on a different instance. I know how to right click on the job and select Script Job as Create. Is there a procedure or something in SQL Server that I can use? I figure I would do a listing of all the SQL Server Jobs and then loop through them scripting them out into an output file. I tried looking for something but could not find anything.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. LiHongMSFT-4306 30,671 Reputation points
    Sep 19, 2022, 2:24 AM

    Hi @LauraC

    To script out all the SQL Server Agent Jobs into one file.

    You could click View --> Object Explorer Details which shows the objects with more details and allows selecting all the jobs together for creating the script.
    242361-image.png
    Refer to this blog: How to script all the SQL Agent Jobs in a single script file

    Also, you could use PowerShell or SP to generate Job scripts. Find more answers in this similar thread: Automatically create scripts for all SQL Server Jobs

    Best regards,
    LiHong


    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".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Jingyang Li 5,896 Reputation points
    Sep 19, 2022, 2:31 AM

    Take some time to learn how to use DBAtools (powershell modules).
    Here is one line of code to get what you need:

    Get-DbaAgentJob -SqlInstance MySQLInstance | Export-DbaScript -FilePath C:\temp\mytemp.sql -Append

    More info:
    https://docs.dbatools.io/Get-DbaAgentJob

    0 comments No comments

  3. LauraC 21 Reputation points
    Sep 20, 2022, 1:26 AM

    JingyangLi,

    Is Get-DbaAgentJob a PowerShell module or is this something that I need to execute and add? I am asking because I am getting the following error:

    Get-DbaAgentJob : The term 'Get-DbaAgentJob' is not recognized as the name of a cmdlet, function, script file, or
    operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try
    again.
    At line:1 char:1

    • Get-DbaAgentJob -SqlInstance MySQLInstance | Export-DbaScript -FilePa ...
    • ~~~~~~~~~~~~~~~
    • CategoryInfo : ObjectNotFound: (Get-DbaAgentJob:String) [], CommandNotFoundException
    • FullyQualifiedErrorId : CommandNotFoundException
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.