Always On

Repair-sqmAlwaysOnDatabases

Scans all Availability Groups on an instance for unhealthy databases and repairs them automatically: remove from AG → drop on secondaries → re-add with AutoSeed. Each repair is written to the Windows Event Log.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes (None)
Output: PSCustomObject[]

Execution Flow

START dbatools available? throw error Invoke-sqmSqlAlwaysOnAutoseeding (enable AutoSeed on all replicas) Connect to instance → enumerate all AGs and their databases foreach database in AG Unhealthy or -Force? skip (healthy) Remove-DbaAgDatabase (remove from AG) Remove-DbaDatabase on each secondary replica Set-DbaDbRecoveryModel -RecoveryModel Full on primary Add-DbaAgDatabase -SeedingMode Automatic Write-EventLog sqmAlwaysOn Collect repair result for this database Return PSCustomObject[] (one entry per repaired database) DONE
Calls Invoke-sqmSqlAlwaysOnAutoseeding first to ensure AutoSeed is enabled before any repair. Each repair action (remove, drop, add) is written to the Windows Event Log source sqmAlwaysOn (created automatically). Use -NoReport to suppress event-log output in scheduled-job contexts.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalPrimary SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-ForceSwitchOptionalAlso repair databases that are already considered healthy (force refresh).
-NoReportSwitchOptionalSuppress Windows Event Log writes (intended for scheduled-job execution).
-EnableExceptionSwitchOptionalThrow terminating errors instead of logging warnings.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns a PSCustomObject[] — one entry per repaired (or skipped) database — with: SqlInstance, AgName, DatabaseName, Action, Status, Message.

Examples

Example 1 — Repair all unhealthy AG databases on local instance

Repair-sqmAlwaysOnDatabases

Example 2 — Force repair of all databases including healthy ones

Repair-sqmAlwaysOnDatabases -SqlInstance "SQL01" -Force

Example 3 — Preview without changes

Repair-sqmAlwaysOnDatabases -SqlInstance "SQL01" -WhatIf