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