On the first day of Christmas dbatools gave to me: Find-DbaCommand
On the second day of Christmas dbatools gave to me: Get-DbaCmsRegServer
On the third day of Christmas dbatools gave to me: DbaMaxMemory
On the fourth day of Christmas dbatools gave to me: DbaAgentJob
Agent Jobs are the heartbeat to most SQL Server environments. Jobs are how we move data, purge data, automate processes, backup databases, perform database maintenance and many other functions. Managing one SQL Server and the Agent Jobs can be a daunting task, much less managing 10, 20, 100 or more. Luckily dbatools has you covered!!! There are a wide range of commands that help you with Agent Jobs. There are commands to Copy jobs, Find Jobs, Get Jobs, Create New Jobs, Remove Jobs, Set Jobs Settings, Start Jobs and Stop Jobs. That is a huge range of functionality, so lets get started on the fourth day goodness.
By default, all jobs are copied. The -Job parameter is auto-populated for command-line completion and can be used to copy only specific jobs.
If the job already exists on the destination, it will be skipped unless -Force is used.
This command filters SQL Agent jobs giving the DBA a list of jobs that may need attention or could possibly be options for removal.
The Get-DbaAgentJob returns connected SMO object for SQL Agent Job information for each instance(s) of SQL Server.
New-DbaAgentJob makes is possible to create a job in the SQL Server Agent.
It returns an array of the job(s) created
Remove-DbaAgentJob removes a job in the SQL Server Agent.
Set-DbaAgentJob updates a job in the SQL Server Agent with parameters supplied.
This command starts a job then returns connected SMO object for SQL Agent Job information for each instance(s) of SQL Server.
This command stops a job then returns connected SMO object for SQL Agent Job information for each instance(s) of SQL Server.
Get-DbaAgentJob -SqlInstance localhost\sql2016 | Out-GridView
Get a list of all Agent Jobs on a server.
Find-DbaAgentJob -SqlInstance localhost\sql2014, localhost\sql2016, localhost\sql2017 -JobName 'Test Job'| Out-GridView
Lets say your boss came to you at the end of the day.
Boss: Can you disable “Test Job”?
Me: Sure, what server does it run from.
Boss: Not sure, can you figure it out?
Me: It might take a while, but I will handle it.
Jokes on you sucker!!!
Advanced Command Usage:
Do you backup your SQL Agent Jobs? Why? Maybe for Disaster Recovery process or just because you are a good DBA, you should have backups of your jobs in case of server crash, accidental deletion, other. Take a look at this command combo for an easy way to protect your jobs.
Get-DbaAgentJob -SqlInstance localhost\sql2016 | Export-DbaScript -Path C:\temp\jobs.sql
Hooray, now you have all the TSQL needed to be protected from an issue where you lose an Agent Job.