· Uwe Janke
Commands / Copy-sqmBackupToArchive

Copy-sqmBackupToArchive

Backup sqmSQLTool v1.9.211+ · Copy ✓ -WhatIf supported
Copies the last full backup of the most recently completed month or year of every database to an archive directory, with all files of a striped backup. -Period Month archives the month-end backups, -Period Year the year-end backups. The period is derived from -ReferenceDate (default: today), so on 3 December Month means November and Year means the previous year.

Always On aware. Backups of an availability group sit on changing nodes, depending on the backup preference, and every msdb knows only its own backups. The function therefore reads the history of all replicas (from sys.availability_replicas), takes the latest full backup per database regardless of which node wrote it, and fetches the files through the admin share of that node. Without an AG it simply uses the given instance.

It will not guess. If a replica cannot be read, the function stops. That replica's msdb may hold exactly the month-end backup, and an older backup from an incomplete history would be the wrong one. Run as a daily job it simply tries again the next day.

Files are copied per file with robocopy /XO, never with /MIR or /PURGE. Nothing in the archive is ever deleted or overwritten, so repeated runs are harmless. Retention of the archive is deliberately not part of this function.

Archive layout

PeriodTarget directory
Month<Destination>\2026\2026-10\<Database>\
Year<Destination>\2026\<Database>\

Characters that are not allowed in a folder name are replaced by _. robocopy creates the directories.

Install it as a job

One daily job on every node, active only on the primary through PrimaryOnly = $true. It picks up the month-end backup as soon as the month is over and does nothing on the days after. For year-end backups install a second job with Period = 'Year'. See New-sqmAgentCommandJob.

Parameters

ParameterTypeRequiredDefaultNotes
-DestinationstringRequired, Archive directory, local or UNC, for example \\fileserver\sqlarchive.
-PeriodstringRequired, Month (month end) or Year (year end).
-ReferenceDatedatetimeOptionaltodayDate the completed period is derived from. For an older period pass a date in the following month or year.
-Databasestring[]OptionalallOnly these databases. Without it: every database with a full backup in the period.
-ExcludeDatabasestring[]Optional, Leave these databases out.
-IncludeSystemDatabasesswitchSwitch$falseAlso master, model and msdb.
-AvailabilityGroupNamestringOptionalall AGsOnly use the replicas of this availability group.
-SqlInstancestringOptional$env:COMPUTERNAMEInstance the replicas are looked up from. In an AG any replica works.
-SqlCredentialPSCredentialOptional, SQL authentication for all connections.
-EnableExceptionswitchSwitch$falseThrow if a copy failed (after all databases were processed) or right away if a replica is unreachable. Set automatically inside Agent jobs.
-WhatIf / -ConfirmswitchOptional, Shows which backup would go where, per database, without copying.

Execution Flow

START Period window last completed month or year, from -ReferenceDate window = [start, end), end = first day of the current month or year Find the replicas all nodes of the AG(s) including the local one, else only -SqlInstance Read msdb history of every replica type D (full), backup_finish_date inside the window all media families of the backup set, so every stripe is known Every replica readable? NO Abort no older backup is picked from incomplete history YES Per database: latest full backup across all replicas system databases left out unless -IncludeSystemDatabases -Database and -ExcludeDatabase applied Backup on a disk file? NO Status Skipped tape, TSM/VDI or URL is only reported YES ShouldProcess? (per database) NO Status WhatIf nothing is copied YES robocopy, one call per file (all stripes) source: \\NODE\X$\path of the node that wrote it, direct if that is the local node target: Destination\yyyy\yyyy-MM\Database\ (Year: Destination\yyyy\Database\) /XO /R:3 /W:10 /NP /NDL: no /MIR and no /PURGE, nothing in the archive is deleted existing files are skipped, so a repeated run copies nothing robocopy exit code >= 8? YES Status Failed stop this database, continue with the next NO Status Copied or UpToDate, then the gap check online user databases of -SqlInstance without any full backup in the window are reported as NoBackup, so a hole in the archive is visible Any database Failed? YES throw (-EnableException) or Write-Error NO DONE

Return value

One object per database with DatabaseName, Period, BackupFinish, SourceReplica, FileCount, TargetPath, Status and Message.

StatusMeaning
CopiedAt least one file of the backup was copied to the archive.
UpToDateAll files were already in the archive.
WhatIf-WhatIf: this backup would have gone to this TargetPath.
NoBackupAn online user database has no full backup in the period. Written to the log as a warning.
SkippedThe backup is not on a disk file (tape, TSM/VDI, URL). The message names the device.
Failedrobocopy exit code 8 or higher. The message carries the code and the file.

The account running the function needs read access to msdb on every replica, read access to the admin share of the nodes, and write access to the archive.

Examples

What would be archived for the last completed month
Copy-sqmBackupToArchive -Destination "\\fileserver\sqlarchive" -Period Month -WhatIf |
    Format-Table DatabaseName, Period, BackupFinish, SourceReplica, FileCount, Status
Year-end backups of 2026, run in January 2027
Copy-sqmBackupToArchive -Destination "\\fileserver\sqlarchive" -Period Year -ReferenceDate "2027-01-15"
Only some databases, system databases included
Copy-sqmBackupToArchive -Destination "D:\Archive" -Period Month `
    -Database "SalesDB","HrDB" -IncludeSystemDatabases
Daily job, active only on the primary
New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "Archiv_Monatsende" `
    -ScheduleType Daily -ScheduleTime "05:00" -Command @{
    FunctionName = "Copy-sqmBackupToArchive"
    Parameters   = @{ Destination = "\\fileserver\sqlarchive"; Period = "Month" }
    PrimaryOnly  = $true
}
Restrict to one availability group
Copy-sqmBackupToArchive -Destination "\\fileserver\sqlarchive" -Period Month -AvailabilityGroupName "AG_Prod"
← Back
Command Index
Related
Copy-sqmBackupToAgPeer
Related
Test-sqmBackupStatus