August 6, 2024 at 11:52 am
I never thought I would ask this question, but here goes.
I need to create a T-SQL script to script out multiple SQL Server Agent jobs on multiple servers. I'm going to pop this into a Powershell script.
I cannot, for the life of me, figure out how to do this without using SSMS. Does anyone have any thoughts on how I could approach this using T-SQL instead?
Or is there perhaps another tool that I can use that will hit multiple servers (without me opening up each server) and to be able to exclude jobs that I know I don't need?
August 6, 2024 at 12:47 pm
easy with SMO
you just need to create a list of servers to extract from and do a foreach loop on that list to call the job function.
August 6, 2024 at 1:08 pm
SMO still works? I thought it got deprecated or removed.
I'll check it out.
August 6, 2024 at 1:09 pm
That article is for SQL 2008. The servers are 2017 and I'm pretty sure SMO code doesn't work anymore in 2017.
EDIT: Even looking at the PowerShell options, it doesn't let me exclude a group of job names. Thank you for the link, but it doesn't give me everything I need.
August 6, 2024 at 1:22 pm
Are you able to use DBATools at all?
They have a ready made function for scripting/copying jobs
August 6, 2024 at 1:51 pm
I have dbatools installed. I'll look at this information. Thanks.
August 6, 2024 at 1:54 pm
Ant-Green, am I missing something? I'm trying to script to a file. That link shows me how to copy from one server to another.
EDIT: I think what I need is Get, not Copy. I'm going to check that command out.
August 6, 2024 at 2:33 pm
That article is for SQL 2008. The servers are 2017 and I'm pretty sure SMO code doesn't work anymore in 2017.
EDIT: Even looking at the PowerShell options, it doesn't let me exclude a group of job names. Thank you for the link, but it doesn't give me everything I need.
excluding or including job names is pure powershell manipulation - it is expected that you know how to do it or know how to search for it. And having a look at the link it ALREADY has an example on how to filter for a job name - so just add similar filter for "NOT like" - and multiple filters can be used.
SMO definitely still works - just tried that same script on a SQL 2019 server, and SMO is still on 2022 documentation as well.
August 6, 2024 at 2:47 pm
Sorry, I thought I read script it to copy somewhere else.
Yeah if you to the Get-DbaAgentJob then you can pipe that to Export-DbaScript
Get-DbaAgentJob -SqlInstance MySQLServer -Job MyBackupJob| Export-DbaScript -Path "C:\temp\MySQLServer_MyBackupJob.sql"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy