· Uwe Janke
Commands / Copy-sqmBackupToAgPeer

Copy-sqmBackupToAgPeer

Backup sqmSQLTool v1.9.210+ · Copy ✓ -WhatIf supported
Copies the backup files of a local directory to the same path on the other Always On replicas. It is meant for backups that live on the node-local disk and must still be there after a failover, for example anonymised databases that another team picks up through the listener while the files sit on whichever node is primary at the moment.

You give it only the source directory. The partner servers come from sys.availability_replicas (the instance part is removed, NODE2\INST1 becomes NODE2; the local replica is skipped), and the target is the partner's admin share with the same path: G:\Backup\Anonym is copied to \\NODE2\G$\Backup\Anonym. With three replicas it copies to both others.

It never deletes and never overwrites a newer file. robocopy runs with /XO and without /MIR or /PURGE. After a failover the partner may hold the younger state, and that must survive. Running it twice is harmless: the second run copies nothing. The default filter *.bak leaves .tmp files of a backup that is still running alone.

Install it as a job

Use New-sqmAgentCommandJob with PrimaryOnly = $true. The same job is installed on every node, and on a secondary the step ends with success without doing anything, so it follows the failover. Add it as a step after the backup, and as a catch-up job for the case that the copy at the end of the backup was cut short by a failover or a patch reboot.

WhenPurpose
Step right after the backupThe partner has the file as soon as it exists.
Catch-up job, for example Saturday and Sunday 06:00Copies what a failover or a rebooting partner prevented. Idempotent, so it costs nothing when everything is there.

Parameters

ParameterTypeRequiredDefaultNotes
-SourcePathstringRequired, Local directory with the backups, for example G:\Backup\Anonym. A drive letter is required, because the admin-share path on the partner is built from it. UNC paths are refused.
-Filterstring[]Optional*.bakFile patterns, for example *_anonym*.bak. Files with another extension, such as .tmp of a running backup, are not copied.
-RecurseswitchSwitch$falseAlso copy sub-folders and keep the folder structure on the partner (robocopy /E). Needed for Ola layouts such as <root>\<Server$AG>\<Database>\FULL; without it only the files directly in the source folder are copied. Still nothing is deleted.
-AvailabilityGroupNamestringOptionalall AGsOnly use the partners of this availability group.
-PartnerServerstring[]Optional, Fix the partner computer names instead of reading them from the AG. For environments without an AG, or for testing.
-SqlInstancestringOptional$env:COMPUTERNAMEInstance the replicas are read from. The source path is read on the machine the function runs on, so run it locally on the node.
-SqlCredentialPSCredentialOptional, SQL authentication for the replica query.
-EnableExceptionswitchSwitch$falseThrow after the last partner if a copy failed, so the Agent job step turns red. Set automatically inside Agent jobs.
-WhatIf / -ConfirmswitchOptional, Shows the target path per partner without copying anything.

Execution Flow

START Source path local, exists? NO throw: drive letter / not found YES Find the partners -PartnerServer, or sys.availability_replicas with is_local = 0 (-AvailabilityGroupName) instance part removed: NODE2\INST1 becomes NODE2 Any partner found? NO throw: no partner replicas YES For each partner ShouldProcess? no: Status WhatIf, nothing is copied robocopy G:\path \\PARTNER\G$\path filter /XO /R:3 /W:10 /NP /NDL no /MIR and no /PURGE: nothing on the partner is deleted or overwritten a newer file on the partner (after a failover) stays as it is robocopy exit code >= 8? YES Status Failed exit code and last 3 output lines in Message NO Status Copied (exit code bit 1) or UpToDate next partner: a failure at one partner never stops the others Any partner Failed? YES throw (-EnableException) or Write-Error, so the Agent job step is red NO Return one object per partner DONE

Return value

One object per partner with SqlInstance, PartnerServer, SourcePath, TargetPath, Status, ExitCode and Message.

StatusMeaning
CopiedAt least one file was copied (robocopy exit code bit 1).
UpToDateEverything was already on the partner. Nothing was copied.
WhatIf-WhatIf: this is the path that would have been written.
Failedrobocopy exit code 8 or higher, for example access denied on the admin share. The message carries the code and the last output lines.

The account running the function (for an Agent job the service account or a proxy) needs write access to the admin share of every partner.

Examples

Copy a directory to the other replicas, partners found automatically
Copy-sqmBackupToAgPeer -SourcePath "G:\Backup\Anonym"
Ola backups with sub-folders (Server$AG\Database\FULL), structure kept on the partner
Copy-sqmBackupToAgPeer -SourcePath 'F:\Daten\MSSQLSERVER\Backup\Usr-DB' -Recurse
Show the target paths first, copy nothing
Copy-sqmBackupToAgPeer -SourcePath "G:\Backup\Anonym" -Filter "*_anonym*.bak" -WhatIf
Extra step in the existing backup job, active only on the primary
New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "Anonym_Backup" -AppendStep -Command @{
    FunctionName = "Copy-sqmBackupToAgPeer"
    Parameters   = @{ SourcePath = "G:\Backup\Anonym"; Filter = "*_anonym*.bak" }
    PrimaryOnly  = $true
}
Catch-up job on Saturday and Sunday morning
New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "Anonym_Backup_Nachkopie" `
    -ScheduleType Weekly -ScheduleDays "Saturday","Sunday" -ScheduleTime "06:00" -Command @{
    FunctionName = "Copy-sqmBackupToAgPeer"
    Parameters   = @{ SourcePath = "G:\Backup\Anonym"; Filter = "*_anonym*.bak" }
    PrimaryOnly  = $true
}
No availability group: name the partner yourself
Copy-sqmBackupToAgPeer -SourcePath "G:\Backup\Anonym" -PartnerServer "NODE2"
← Back
Command Index
Install as job
New-sqmAgentCommandJob
Related
Copy-sqmBackupToArchive