Copy-sqmBackupFile

Backup sqmSQLTool v1.9.216+ · Copy ✓ -WhatIf supported

Copies one backup file of an Ola Hallengren backup folder to a target, optionally under another name. When it happens is decided by the SQL Server Agent job (schedule, weekday); the function knows no date.

Source. You give it the Ola root directory and the database name. Below the root it expects Ola's layout <root>\<Database>\FULL, \DIFF or \LOG and searches there with a wildcard, because Ola appends date and time to every name (SERVER$AG_arenap01_HST_FULL_20261103_042816.bak). The default pattern is *.bak for Full and Diff and *.trn for Log; -SourceFile overrides it.

Which node. The source path is the same on both Always On nodes. The function looks in the local directory directly and on the other replicas through their admin share, and takes the file with the newest modification time, whichever node holds it. On a tie the local node wins. With striped backups this is the newest single file.

Target. -TargetPath and optionally -TargetFile. Without a file name the source name is kept. Both accept {Database} and {Type} (FULL, DIFF, LOG), so one rule can serve several databases. The copy goes through a temporary file (<target>.tmp) and a rename, so no half-written file ever carries the final name.

Existing target

Situation	Result
Target is identical (same size and modification time)	`UpToDate`. A second run does nothing.
Target differs, no `-Overwrite`	`Exists` (an error). The target stays as it is.
Target differs, `-Overwrite`	Replaced, again through a temporary file and rename. If the rename fails the old target stays and the temporary file is removed.

The copy keeps the modification time of the source, which is what makes the second run recognise an identical target.

Install it as a job

With New-sqmAgentCommandJob and PrimaryOnly = $true, scheduled on the weekday you need. Note the single quotes: the $ in Ola's folder name would otherwise be expanded by PowerShell.

Parameters

Parameter	Type	Required	Default	Notes
-SourcePath	string	Required	,	Ola root directory, a local path with drive letter, for example `F:\Daten\MSSQLSERVER\Backup\Usr-DB\SERVER1$AG1`. Use single quotes.
-Database	string[]	Required	,	Database name(s). The name is also the sub-folder under `-SourcePath`.
-BackupType	string[]	Optional	Full	`Full`, `Diff` and/or `Log`. One file per database and type. Full and Diff look in `FULL` and `DIFF`, Log in `LOG`.
-SourceFile	string	Optional	.bak / .trn	File name wildcard, no path. Overrides the default pattern, for example `_HST_FULL_.bak`.
-TargetPath	string	Required	,	Target directory, local or UNC. Created if missing. `{Database}` and `{Type}` are replaced.
-TargetFile	string	Optional	source name	Target file name. `{Database}` and `{Type}` are replaced. A fixed name without a placeholder is refused when several databases or types would write to it.
-Overwrite	switch	Switch	$false	Replace a differing existing target.
-AvailabilityGroupName	string	Optional	all AGs	Only use the replicas of this availability group.
-PartnerServer	string[]	Optional	,	Fix the other nodes instead of reading them from the AG.
-SqlInstance	string	Optional	$env:COMPUTERNAME	Instance the replicas are read from. Run the function locally on a node.
-SqlCredential	PSCredential	Optional	,	SQL authentication for the replica query.
-EnableException	switch	Switch	$false	Throw after all entries were processed so the Agent job step turns red. Set automatically inside Agent jobs.
-WhatIf / -Confirm	switch	Optional	,	Shows which file would go where, without copying.

Execution Flow

Return value

One object per database and type with Database, BackupType, SourceNode, SourceFile, SourceModified, SizeMB, Target, Status and Message.

Status	Meaning
`Copied`	The file was copied (or replaced with `-Overwrite`).
`UpToDate`	The target is already identical. Nothing was done.
`WhatIf`	`-WhatIf`: this file would have gone to this `Target`.
`NotFound`	No file matched the pattern in the Ola folder on any node.
`Exists`	The target exists and differs, and `-Overwrite` was not given.
`Failed`	Copying or renaming failed. The message names the reason; the temporary file is removed.

The account running the function (for an Agent job the service account or a proxy) needs read access to the admin share of the other nodes and write access to the target.

Examples

What would be copied: the newest Full of one database from either node

Copy-sqmBackupFile -SourcePath 'F:\Daten\MSSQLSERVER\Backup\Usr-DB\SERVER1$AG1' `
    -Database arenap01_HST -TargetPath '\\fs\archive\arena' -WhatIf |
    Format-List Status, SourceNode, SourceFile, Target

Fixed target name, replaced every week

Copy-sqmBackupFile -SourcePath 'F:\Daten\MSSQLSERVER\Backup\Usr-DB\SERVER1$AG1' `
    -Database arenap01_HST -TargetPath '\\fs\archive\arena' `
    -TargetFile '{Database}_Weekly.bak' -Overwrite

Several databases, Full and Log, one folder per database, source names kept

Copy-sqmBackupFile -SourcePath 'F:\Daten\MSSQLSERVER\Backup\Usr-DB\SERVER1$AG1' `
    -Database DB1,DB2 -BackupType Full,Log -TargetPath '\\fs\archive\{Database}'

Agent job step on Sunday morning, active only on the primary

New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "Archive_Weekly" `
    -ScheduleType Weekly -ScheduleDays "Sunday" -ScheduleTime "06:00" -Command @{
    FunctionName = "Copy-sqmBackupFile"
    Parameters   = @{
        SourcePath = 'F:\Daten\MSSQLSERVER\Backup\Usr-DB\SERVER1$AG1'
        Database   = 'arenap01_HST'
        TargetPath = '\\fs\archive\arena'
        TargetFile = '{Database}_Weekly_{Type}.bak'
        Overwrite  = $true
    }
    PrimaryOnly  = $true
}

A different file pattern instead of the default

Copy-sqmBackupFile -SourcePath 'F:\Daten\MSSQLSERVER\Backup\Usr-DB\SERVER1$AG1' `
    -Database arenap01_HST -SourceFile '*_HST_FULL_202611*.bak' -TargetPath 'D:\Out'

← Back

Command Index

Copy-sqmBackupToAgPeer

Copy-sqmBackupToArchive