Always On

Repair-sqmAlwaysOnDatabases

Detects AG databases whose synchronization state is not HEALTHY or SYNCHRONIZED, then repairs each one via a Remove → Drop-on-secondaries → Set-FULL-recovery → Re-add with AutoSeed cycle. Used as the back-end of the scheduled sqmAutoAlwaysOnRepair Agent job.

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

Execution Flow

START dbatools available? throw error Invoke-sqmSqlAlwaysOnAutoseeding -All (ensure Automatic Seeding on all replicas) Get-DbaAvailabilityGroup + Get-DbaAgDatabase → scan all AGs Identify: SyncState ≠ HEALTHY/SYNCHRONIZED (or -Force) Problematic DBs found? None found → return Yes foreach problematic database Remove-DbaAgDatabase (remove from AG, write EventLog 1000) Remove-DbaDatabase on each secondary replica Set-DbaDbRecoveryModel Full (if not already Full) Add-DbaAgDatabase -SeedingMode Automatic (write EventLog 1001) Status=RepairSuccess (or RepairFailed on error) Return PSCustomObject[] (all repair results) DONE
Each repair step is written to the Windows Application event log (source sqmAlwaysOn, EventId 1000=start / 1001=success / 1002=failure). The source is auto-created on first run. The function is called by the New-sqmAlwaysOnRepairJob hourly Agent job.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalPrimary replica SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-ForceSwitchOptionalRepairs all AG databases, including those that are currently HEALTHY (forced refresh).
-NoReportSwitchOptionalSkips report generation (intended for use in scheduled job context).
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning an error status object.
-WhatIfSwitchOptionalShows what would be repaired without making changes.

Return Value

Returns an array of PSCustomObject, one per repaired database, each with: SqlInstance, DatabaseName, AvailabilityGroup, Status (RepairSuccess / RepairFailed), Message.

Examples

Example 1 — Repair all unhealthy AG databases

Repair-sqmAlwaysOnDatabases -SqlInstance "SQL01"

Example 2 — Force repair all databases (including healthy)

Repair-sqmAlwaysOnDatabases -SqlInstance "SQL01" -Force