This chapter moves from simply querying SQL Server Agent to confidently creating and managing its objects with dbatools. It shows how to discover the New/Set/Remove commands that target Agent components and then applies them to build reliable, automated workflows. The overarching goal is to schedule dbatools scripts with SQL Server Agent using a stable, repeatable approach that works across one or many instances, while keeping administration tidy and consistent.
The authors walk through the full job-assembly process: creating categories to organize jobs and alerts, defining schedules for common patterns (daily, monthly, and weekday windows) and precise sub-day intervals, and choosing non-round start times to avoid contention. They emphasize least-privilege execution by creating credentials and proxies (for specific subsystems like CmdExec), and add operators for email notifications. With those building blocks in place, they create the job itself—setting owner, category, schedule, and alerting—and then add ordered job steps. Step creation highlights controlling flow with success/failure actions, explicitly numbering steps to preserve order, inserting steps between existing ones, and verifying results.
For running PowerShell reliably, the chapter recommends CmdExec steps that invoke powershell.exe with a saved script file, whether on local storage or a UNC path, and reminds you to grant the proxy proper access. It demonstrates combining everything into a production-ready job, including appending CmdExec output to job history for easier troubleshooting. Finally, it covers day-two operations: starting jobs programmatically (optionally waiting for completion), listing jobs running across servers (via registered servers or explicit targets) and piping to stop them if needed, and reviewing detailed history from msdb. Together, these practices enable robust, scalable automation of dbatools tasks across your SQL Server estate.
Hands-on Lab
Try the following tasks
- Create a schedule that runs jobs every day at 6 in the morning
- View the job history on multiple SQL instances
- Find all jobs that are currently running within your estate
- Use
Get-Help Start-DbaAgentwith the-Parameterparameter to get more information aboutWaitandWaitPeriod.
FAQ
How can I quickly list all dbatools commands that create, change, or remove SQL Agent objects?
Use PowerShell’s discovery:Get-Command -Module dbatools -Verb New,Set,Remove -Noun *Agent*. This works for any module, not just dbatools.What’s the easiest way to create the same Agent category on multiple SQL instances?
Pass multiple instances to-SqlInstance, for example: New-DbaAgentJobCategory -SqlInstance SQL01,SQL02 -Category MyCategory. By default it creates a LocalJob category; specify -CategoryType MultiServerJob if needed.How do I define a schedule and what format does -StartTime expect?
UseNew-DbaAgentSchedule and provide -StartTime in 24-hour HHMMSS (e.g., 000248). Avoid “round” times to reduce contention. For sub-day frequency, combine -FrequencySubdayType (Seconds|Minutes|Hours|Time) with -FrequencySubdayInterval; with Time, use a timespan like 063300 for 6h 33m.How do I create a credential and proxy for running CmdExec/PowerShell job steps?
Create a credential (e.g., viaGet-Credential + New-DbaCredential), then a proxy with New-DbaAgentProxy -SubSystem CmdExec. Ensure the proxy account has access to any local/UNC paths; consider retrieving secrets from a vault rather than typing passwords.How do I set up an operator for email notifications?
Create one withNew-DbaAgentOperator -Operator "DBA Team" -EmailAddress operator@domain.com, then reference it in the job via -EmailOperator and choose notification levels with -EmailLevel. Ensure Database Mail/Agent mail is configured.How do I create a new job and attach schedule, category, owner, and notifications?
UseNew-DbaAgentJob with parameters like -Job, -Schedule (existing schedule name), -Category, -OwnerLogin, -EmailOperator, -EventLogLevel, and -EmailLevel. After creation, add steps—until then, the job has no HasStep.What’s the right way to add ordered T-SQL steps and control flow between them?
UseNew-DbaAgentJobStep and set -StepId explicitly to control order. Manage flow with -OnSuccessAction and -OnFailAction. To insert between steps, use -Insert. Verify order with Get-DbaAgentJobStep.What’s the recommended way to run PowerShell from SQL Agent?
Use a CmdExec step that calls PowerShell directly, for example:powershell.exe -File \\share\script.ps1. Run it under a least-privilege proxy and ensure the proxy can reach the path.How can I start and monitor jobs across many servers from PowerShell?
Pipe jobs toStart-DbaAgentJob, e.g., Get-DbaAgentJob -SqlInstance SQL01,SQL02 -Job "My Job" | Start-DbaAgentJob. Use -Wait/-WaitPeriod to block until completion. See current activity with Get-DbaRunningJob (against Registered Servers or explicit -SqlInstance) and, if needed, pipe to Stop-DbaAgentJob.How do I capture and review job output and history?
Add PowerShell/CmdExec output to history by passing-Flag AppendAllCmdExecOutputToJobHistory when creating the step. Retrieve history with Get-DbaAgentJobHistory (optionally filter by -Job). Note it queries msdb and may take time on large histories.
Learn dbatools in a Month of Lunches ebook for free
