· Uwe Janke
Commands / Repair-sqmAlwaysOnDatabases
Always On

Repair-sqmAlwaysOnDatabases

Scans the Availability Groups in which the instance is primary 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 Enumerate AGs → keep only those where this instance is primary Invoke-sqmSqlAlwaysOnAutoseeding for these AGs → list 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
Primary only (v1.9.152+): AGs in which the instance is a secondary are skipped and logged together with the actual primary. Removing and re-adding a database only works on the primary; on a secondary, Remove-DbaAgDatabase would take the local copy out of the AG and the re-add would fail. If the instance is primary of no AG, nothing is changed. Run the job on every node and each node repairs the AGs it is currently primary of, which keeps working after a failover.
Calls Invoke-sqmSqlAlwaysOnAutoseeding for those AGs 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, DatabaseName, AvailabilityGroup, Status (RepairSuccess, RepairFailed, RepairSkipped, GlobalError), 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
⬆ Index
All Commands