Sql-server – SQL Server: job with PowerShell step with robocopy doesn’t end

sqlsql server

I have a multiple steps SQL job set up in that basically generates transactions logs and then copies them using robocopy. Robocopy steps are of type “PowerShell” and look like following:

$src=(Invoke-Sqlcmd -Database 'msdb' -Query "select last_backup_file from log_shipping_monitor_primary where primary_database = 'Dummy'")['last_backup_file']
$command="robocopy"
$arguments=([System.IO.Path]::GetDirectoryName($src)) + " \\server\share " + ([System.IO.Path]::GetFileName($src)) + " /NP"
[System.Diagnostics.Process]::Start($command, $arguments)

The issue with this step specifically is that robocopy never exits, process stays active, even after the job’s run succedeed. If same code above is ran from SQL PowerShell console (or a non-SQL PowerShell console with SQL snap-ins loaded), it runs correctly, no orphan process is left alive.

Note: you may wonder why I run robocopy through Process.Start(). That brings me to the first issue: when script looks like following (which I tried before the script above), job execution never stops:

$src=(Invoke-Sqlcmd -Database 'msdb' -Query "select last_backup_file from log_shipping_monitor_primary where primary_database = 'Dummy'")['last_backup_file']
robocopy ([System.IO.Path]::GetDirectoryName($src)) \\server\share ([System.IO.Path]::GetFileName($src)) /NP

Note (2): I read Fixing robocopy for SQL Jobs but this issue is unrelated.

Best Answer

The issue was simply that robocopy needed this parameter: "/R:0".

Really weird, since that indicates to not retry the operation. But operation was succeeding the first time. And again, from PowerShell console, it worked correctly.