Automation

New-sqmAgentCommandJob

Generic SQL Server Agent job/step builder for any exported sqmSQLTool function. Parameters are never embedded as text in generated PowerShell source — each step's parameters are serialized via Export-Clixml and loaded by a single reusable wrapper (generic-invoke.ps1) that resolves the target function through Get-Command (acting as an allowlist) and invokes it via splatting.

Module: sqmSQLTool

Requires: dbatools

ShouldProcess: Yes

Output: PSCustomObject

Why not just build the argument string by hand?

The module's earlier per-function job builders (New-sqmRestoreDatabaseJob, New-sqmAlwaysOnRepairJob, ...) each hand-roll their own argument-string generator for one fixed target function. An earlier generic attempt (Private\New-sqmCmdExecJobStep.ps1) tried to generalize this by embedding parameter values into the generated wrapper via double-quoted string interpolation — it broke on $ characters in paths and carried hardcoded flags (-Verbose -ContinueOnError) that not every function accepts, and was abandoned. New-sqmAgentCommandJob solves this structurally: parameter values are never turned into PowerShell source text at all.

Execution Flow

Two independent choices, both supported: new job vs. appending step(s) to an existing job (-AppendStep), and on-demand vs. scheduled (-ScheduleType Daily/Weekly/Monthly via New-DbaAgentSchedule, same pattern as New-sqmRestoreTestJob). When appending, the previously-last step's OnSuccessAction is switched from QuitWithSuccess to GoToNextStep automatically — otherwise the job would silently stop before ever reaching the newly appended step(s).

Parameters

Parameter	Type	Required	Description
-SqlInstance	String	Optional	Target SQL instance where the job is created/extended and where the wrapper/params files are written. Default: `$env:COMPUTERNAME`. Run this ON the target instance.
-SqlCredential	PSCredential	Optional	SQL credential for the connection used to CREATE the job (only needed without Windows-integrated auth / domain trust to the target). Has no effect on how the job step itself authenticates once created.
-JobName	String	Required	Name of the Agent job.
-Command	Hashtable[]	Required	One or more hashtables describing a step, in execution order: `FunctionName` (required, must be an exported sqmSQLTool function), `Parameters` (optional hashtable, splatted), `StepName` (optional, default is the function name).
-AppendStep	Switch	Optional	Add the `-Command` step(s) to an EXISTING job instead of creating a new one. Requires the job to already exist.
-Force	Switch	Optional	When NOT using `-AppendStep`: replace an existing job of the same name. Ignored with a warning if combined with `-AppendStep`.
-ScheduleType	String	Optional	'None' (default, on-demand only), 'Daily', 'Weekly' or 'Monthly'.
-ScheduleTime	String	Optional	Time of day for the schedule, "HH:mm". Default: '02:00'.
-ScheduleDays	String[]	Optional	Weekday(s) for `-ScheduleType Weekly`, e.g. 'Monday','Thursday'. Mandatory for Weekly.
-ScheduleDayOfMonth	Int	Optional	Day of month (1-28) for `-ScheduleType Monthly`. Default: 1.
-StartJob	Switch	Optional	Start the job immediately after creating/extending it.
-EnableException	Switch	Optional	Throw exceptions immediately instead of logging and returning a result object.
-WhatIf / -Confirm	Switch	Optional	Standard ShouldProcess support — dry-runs the entire plan, including allowlist validation, without writing any file or touching the Agent job.

Return Value

Returns a PSCustomObject with: SqlInstance, JobName, Mode (NewJob or AppendStep), Steps (array of StepId/StepName/FunctionName/ParamsPath), ScheduleName, Started, Status, Timestamp.

Examples

Example 1, Single step, on-demand

New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "sqmCmd_LoginCompare_AG1" -Command @{
    FunctionName = 'Compare-sqmAlwaysOnLogins'
    Parameters   = @{ SqlInstance = 'SQL01'; AvailabilityGroupName = 'AG1'; OnlyDifferences = $true; FailOnDrift = $true; NoReport = $true }
}

Example 2, Two steps chained in one job, scheduled daily at 03:00

New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "sqmCmd_AGMaintenance" -ScheduleType Daily -ScheduleTime "03:00" -Command @(
    @{ FunctionName = 'Compare-sqmAlwaysOnLogins';   Parameters = @{ SqlInstance = 'SQL01'; AvailabilityGroupName = 'AG1'; FailOnDrift = $true } },
    @{ FunctionName = 'Repair-sqmAlwaysOnDatabases'; Parameters = @{ SqlInstance = 'SQL01'; AvailabilityGroupName = 'AG1' } }
)

Example 3, Append a step to that same job later

New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "sqmCmd_AGMaintenance" -AppendStep -Command @{
    FunctionName = 'Get-sqmAlwaysOnHealthReport'
    Parameters   = @{ SqlInstance = 'SQL01' }
}

⬆ Index

All Commands