Commands / Add-sqmDatabaseToDistributedAg
Add-sqmDatabaseToDistributedAg
Always On sqmSQLTool v1.8.2+ · Add ✓ -WhatIf supported
Adds a database to a Distributed Always On Availability Group in a defined sequence of steps: verify the database on the primary, create a compressed full backup, follow up with a log backup, restore both to the secondary instance using NORECOVERY, join the restored database to the secondary AG, and finally monitor synchronization state.

Each step is recorded in a Steps list on the returned [PSCustomObject]. On success the object carries Status = 'SUCCESS'; any unhandled error returns Status = 'FAILED' with the error message.

Prerequisites: A secondary AG and the Distributed AG relationship must already be established. The -BackupPath must be reachable from both instances (UNC share or local path with appropriate permissions).

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMEPrimary SQL Server instance (source). Accepts pipeline input.
-AvailabilityGroupNamestringRequired—Name of the Distributed AG.
-DatabaseNamestringRequired—Database to add to the Distributed AG.
-SecondaryInstancestringRequired—SQL Server instance on the secondary cluster where the database is restored.
-BackupPathstringOptionalC:\BackupsDirectory for full (.bak) and log (.trn) backup files. Created automatically if not present.
-SqlCredentialPSCredentialOptional—Used for all connections (primary and secondary).
-EnableExceptionswitchSwitch$falseRe-throw errors instead of returning a FAILED result object.
-WhatIf / -ConfirmswitchOptional—Standard ShouldProcess support (ConfirmImpact: High).

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME BackupPath exists? NO New-Item -ItemType Directory -Force YES DB exists on primary? NO throw: DB not found YES catch (outer): log error return Status='FAILED' Step 1 — Full Backup BACKUP DATABASE TO DISK WITH COMPRESSION, INIT Step 2 — Log Backup BACKUP LOG TO DISK WITH COMPRESSION, INIT Step 3 — Restore on Secondary RESTORE DATABASE WITH NORECOVERY, REPLACE RESTORE LOG WITH NORECOVERY Step 4 — Join to Secondary AG ALTER DATABASE SET HADR AVAILABILITY GROUP = [AG] Step 5 — Monitor Synchronization Start-Sleep 5s → query dm_hadr_database_replica_states record synchronization_state_desc + synchronization_health_desc Return [PSCustomObject] Status='SUCCESS' DatabaseName, AvailabilityGroup, BackupFile, LogBackupFile, Steps[] DONE

Examples

Add a database to a Distributed AG via a shared backup path
Add-sqmDatabaseToDistributedAg `
    -SqlInstance "SQL01" `
    -AvailabilityGroupName "MyDAG" `
    -DatabaseName "MyDb" `
    -SecondaryInstance "DR-SQL01" `
    -BackupPath "\\FileServer\SQLBackups"
Capture step results and check overall status
$result = Add-sqmDatabaseToDistributedAg -SqlInstance "SQL01" -AvailabilityGroupName "MyDAG" `
          -DatabaseName "MyDb" -SecondaryInstance "DR-SQL01"

if ($result.Status -ne 'SUCCESS') {
    Write-Warning "Failed: $($result.Error)"
    $result.Steps | Format-Table
}
← Back
Command Index
Related
Add-sqmDatabaseToAG