Using TSQL How to Script All SQL Server Agent Jobs into One File

Question

I am trying to find TSQL that will script all the SQL Server Agent Jobs into one File. I haven't found anything yet. Can someone point me to an URL that I can look into?

We are uninstalling SQL Server Standard and installing SQL Server Developer, thus, I have to script out the jobs into a file on a directory in a server before the uninstall and after installing SQL Server Developer then I can execute the file.

Answer 1

Hi @LauraC ,
You don't need to just use TSQL. How about trying this way:

1. Click on the "Jobs" section in the SQL Agent in SSMS
2. Hit the F7 key on your keyboard (opens the Object Explorer Details)
3. Highlight the jobs you want to export in the Object Explorer Details
4. Right-click the highlighted jobs and "create..."

Answer 2

It's possible with plain T-SQL script to export the job as script. Or using SSMS => CREATE SCRIPT option.

The easier way is to use PowerShell dbatools-CmdLet to do so:
https://docs.dbatools.io/Export-DbaScript

Answer 3

We are automating the process. An Ansible Job will submit a command to script out the SQL Server Agent Jobs and place the file in a directory. My manager does not want us to do this manually.

Answer 4

Hi @LauraC
To generate scripts for multiple objects (agent jobs), you can write a procedure, which builds a cursor with all the object names you want scripted and call proc_genscript one by one.
Refer to this blog for more details: Generate Scripts for SQL Server Objects

Also, you might find something useful 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.

Answer 5

I have tried to run and I am getting an error.

If the SQL Server Instance is USCTAPD66000\DEV01. And I saved Export-DbaScript.ps in directory C:\Users\xxx. Should the command then be

PS C:\Users\xxx> Get-DbaAgentJob -SqlInstance USCTAPD66000\DEV01 | Export-DbaScript -FilePath C:\Users\xxx\export.sql -Append
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 USCTAPD66000\DEV01 | Export-DbaScript - ...
• ~~~~~~~~~~~~~~~
• CategoryInfo : ObjectNotFound: (Get-DbaAgentJob:String) [], CommandNotFoundException
• FullyQualifiedErrorId : CommandNotFoundException