Commands / New-sqmAlwaysOnRepairJob
Always On

New-sqmAlwaysOnRepairJob

Creates a SQL Server Agent job that runs Repair-Job.ps1 via a CmdExec job step on an hourly schedule. Designed to automatically repair AG synchronization issues. Sets up the output directory with appropriate NTFS permissions for SQL Agent service accounts.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes
Output: PSCustomObject

Execution Flow

START dbatools available? No throw error Yes New-Item -ItemType Directory (OutputPath) + icacls for SQL Agent accounts ShouldProcess? WhatIf Skip -Force & job exists? Yes Remove-DbaAgentJob (-Force) New-DbaAgentJob (-JobName, -Description, -Category) New-DbaAgentJobStep (SubsystemCmdExec: powershell.exe -File Repair-Job.ps1 -OutputPath ...) sp_add_schedule (hourly, freq_interval=1, -ScheduleName) sp_attach_schedule (link schedule to job) Verify: Get-DbaAgentJob → status Job found on instance? No Status=CreateFailed Return PSCustomObject (Status=Created) DONE
The job step runs powershell.exe -File Repair-Job.ps1 via a CmdExec subsystem step. The output directory is created first and its NTFS ACL is updated to grant the SQL Server Agent service accounts write access, ensuring job output files can be written even under least-privilege configurations.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL Server instance. Default: $env:COMPUTERNAME.
-JobNameStringOptionalAgent job name. Default: sqmTool_AlwaysOn_AutoRepair.
-ForceSwitchOptionalRemove and recreate the job if it already exists.
-WhatIfSwitchOptionalPreview actions without creating the job or directory.
-ConfirmSwitchOptionalPrompt before creating objects.

Return Value

Returns a PSCustomObject with: SqlInstance, JobName, ScheduleName, RepairScriptPath, OutputPath, Status (Created / Replaced / CreateFailed / WhatIfSkipped), Message.

Examples

Example 1, Create repair job with defaults

New-sqmAlwaysOnRepairJob -SqlInstance "SQL01" `
    -RepairScriptPath "C:\DBA\Scripts\Repair-Job.ps1"

Example 2, Custom job name, output path, and force replace

New-sqmAlwaysOnRepairJob -SqlInstance "SQL01" `
    -RepairScriptPath "C:\DBA\Scripts\Repair-Job.ps1" `
    -JobName "AG_AutoRepair_Finance" `
    -OutputPath "D:\AgentOutput\AGRepair" `
    -Force

Example 3, WhatIf preview

New-sqmAlwaysOnRepairJob -SqlInstance "SQL01" `
    -RepairScriptPath "C:\DBA\Scripts\Repair-Job.ps1" -WhatIf
⬆ Index
All Commands