"Why Does My PowerShell Function Fail to Copy and Move Files When Scheduled with Windows Scheduler or SQL Server Job Agent?"

Hi all,

I’m facing an issue with Windows Scheduler and SQL Server Jobs. I have a PowerShell function that is designed to copy and move files from a local location on a VM to an Azure Long Term Backup Storage Location. When I run this PowerShell function from PowerShell ISE, it works perfectly and performs the file copy and move operations as expected.

However, when I schedule the same function to run using Windows Scheduler or SQL Server Jobs, it appears to execute without errors, but it does not copy or move the files as intended. The task completes without any visible issues or error messages.

I’ve verified that the scheduled task runs under the same user account (Administrator) as when I run it manually in PowerShell ISE, so I’m puzzled as to why it doesn’t work as expected when scheduled.

Has anyone encountered a similar issue or can offer insights into what might be causing this behavior? I’d greatly appreciate any help or suggestions to resolve this problem.

Thank you!

bahaa,
Welcome to the forum. :wave:t3:

We can hardly recommend something meaningful without seeing your actual code. :wink:

Are you using your account to run the task? Should it run without you beeing logged on to the the computer. Is your account allowed to “log on as batch job”

You say “function” - do you actually call your function or do you just define it? :smirk:

I tried multiple ways to call the PowerShell function from a Windows Scheduler as:

-Command “& 'C:\Scripts\udfMoveFilesToAzurePRD.ps1” -SourcePath ‘E:\MSSQL\MSSQL13.RPRD01\MSSQL\Backup’ -DestinationPath ‘Z:\DB\MSSQL\RWE\PRD’ -LogFilePath ‘C:\Scripts\MoveFilesPRDLog.txt’"

I tried another way like this:

-File “udfMoveFilesToAzurePRD.ps1” -SourcePath “E:\MSSQL\MSSQL13.RPRD01\MSSQL\Backup” -DestinationPath “Z:\DB\MSSQL\RWE\PRD” -LogFilePath “C:\Scripts\MoveFilesPRDLog.txt”

I even used a Batch file with this PowerShell Function and run it from the Task Scheduler … all the tries returns No Error , but it did not do the intended work? , only if I run it from PowerShell ISE it works fine !..
any idea?

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org 1 <---- Click :point_up_2:t4: :wink:

( !! Sometimes the preformatted text button hides behind the settings gear symbol. :wink: )

Please show your code.

I tried:

 -Command “& 'C:\Scripts\udfMoveFilesToAzurePRD.ps1” -SourcePath ‘E:\MSSQL\MSSQL13.RPRD01\MSSQL\Backup’ -DestinationPath ‘Z:\DB\MSSQL\RWE\PRD’ -LogFilePath ‘C:\Scripts\MoveFilesPRDLog.txt’"

OR:

-File “udfMoveFilesToAzurePRD.ps1” -SourcePath “E:\MSSQL\MSSQL13.RPRD01\MSSQL\Backup” -DestinationPath “Z:\DB\MSSQL\RWE\PRD” -LogFilePath “C:\Scripts\MoveFilesPRDLog.txt”

I tried also a batch file like this:

@echo off
powershell.exe -File "C:\Scripts\udfMoveFilesToAzurePRD.ps1" -SourcePath "E:\MSSQL\MSSQL13.RPRD01\MSSQL\Backup" -DestinationPath "Z:\DB\MSSQL\RWE\PRD" -LogFilePath "C:\Scripts\MoveFilesPRDLog.txt" > "C:\Scripts\Logs\PowerShellOutput.txt" 2>&1
timeout /t 10 /nobreak

it runs it without Error the files stays on the local location?

Since this is a PowerShell forum I was refering to your PowerShell code … the content of your script udfMoveFilesToAzurePRD.ps1
Please share your code! … not the command line you use to run the script!!! :point_up:t3: :smirk:

As I already said that the PowerShell Function “udfMoveFilesToAzurePRD” is working on he PowerShell ISE, But Not on Windows Scheduler or SQL Server Jobs Agent, and sharing the code is not part of the question itself , never the less here is the code:

function udfMoveFilesToAzurePRD {
     param (
         [string]$SourcePath=  "E:\MSSQL\MSSQL13.RPRD01\MSSQL\Backup"
         ,[string]$DestinationPath= "Z:\DB\MSSQL\RWE\PRD"
         ,[string]$LogFilePath= "C:\Scripts\MoveFilesPRDLog.txt"
     )
 try{
     # Set the execution policy to Unrestricted within your script
     Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process

     # Check if the specified source path exists
     if (-not (Test-Path -Path $SourcePath -PathType Container)) {
         Write-Host "Source path does not exist."
         return
     }
 
     # Check if the specified destination path exists, and create it if not
     if (-not (Test-Path -Path $DestinationPath -PathType Container)) {
         New-Item -Path $DestinationPath -ItemType Directory -Force | Out-Null
     }
 
     # Use RoboCopy to move files from source to destination with logging
     $RoboCopyArgs = "/MOV /MINAGE:8 /IS /LOG:$LogFilePath /TEE"
     $RoboCopyCommand = "RoboCopy `"$SourcePath`" `"$DestinationPath`" *.* $RoboCopyArgs"
     
     Invoke-Expression -Command $RoboCopyCommand
 
     # Parse the RoboCopy log to extract the moved files
     $MovedFiles = Get-Content -Path $LogFilePath | Where-Object { $_ -match '^  (\S+) -> (\S+)' } | ForEach-Object {
         $Matches[1] | Out-Null
         $SourceFile = $Matches[1]
         $Matches[2] | Out-Null
         $DestinationFile = $Matches[2]
         [PSCustomObject]@{
             SourceFile = $SourceFile
             DestinationFile = $DestinationFile
         }
     }
 
     # Output the list of moved files
     if ($MovedFiles.Count -gt 0) {
         Write-Host "Moved Files:"
         $MovedFiles | ForEach-Object {
             Write-Host "Source: $($_.SourceFile) -> Destination: $($_.DestinationFile)"
         }
     }
     }

                   catch {
         # Handle any errors
         $errorMessage = "Error: $_"
         Write-Error $errorMessage
         Write-Output "ERROR: $errorMessage"
     }

     Finally{
     # Reset the execution policy to its previous value (optional)
     Set-ExecutionPolicy -ExecutionPolicy Restricted -Scope Process
     }
 }
 
 # Example usage:  
 $SourcePath = "E:\MSSQL\MSSQL13.RPRD01\MSSQL\Backup"
 $DestinationPath = "Z:\DB\MSSQL\RWE\PRD"
 $LogFilePath = "C:\Scripts\MoveFilesPRDLog.txt" 
 udfMoveFilesToAzurePRD -SourcePath $SourcePath -DestinationPath $DestinationPath -LogFilePath $LogFilePath

any idea?

Is this the exact script you run with your scheduled task??? :thinking:

Yes, as I already mentioned , it works fine with PowerShell ISE , But Not with SQL Server Job Agent or Windows Scheduler
The Problem is I’ve got No Errors to trace or debug , therefor I do not know what to do?

In the code you posted you are defining a function and you call this function at the end of the script underneath the comment # Example usage: with predefined values. The values you provide on the command line are never used. :point_up:t3:
I’d remove the function definition around the code and use the script as it is. So remove function udfMoveFilesToAzurePRD { and the closing curly brace at the end of the function and remove the example code at the end of the script.

That’s correct. Since you only output status information to the console which you don’t see at runtime you don’t have errors or trace or debug. :man_shrugging:t3: How about writing a log file?

And BTW:

That does not make any sense at all. It is like the chicken and egg problem. You cannot control the executionpolicy for scripts from inside a script. :smirk:

You have a point here for this :“That does not make any sense at all. It is like the chicken and egg problem. You cannot control the Execution Policy for scripts from inside a scrip”, but I did it because when I used the Action Tab of the scheduler , I put PowerShell -ExecutionPolicy …etc , didn’t help , therefor I thought maybe when put it inside the code it will work!.
The #usage Example is only to show how I run it in PowerShell ISE , but when I use it in the task scheduler , it has NO Usage Example , just the name of the function itself (Without Usage Example).
For Not using a Function and use only a script , I need the Function to write in the log all the names of the copied or moved files , this is part of the Business Requirement , to be sure that all Backup files (.bak, .fullback & .trn) are copied or moved to Azure Storage.

What is the solution for using Multi Parameter PowerShell Function in Windows Scheduler Task or SQL Server Job Agent ?

You might have targeted the wrong issue with it. :man_shrugging:t3:

I will not.

That’s why I asked if that’s the correct code you’re using. Again - we cannot see your screen and we cannot read your mind. We heavily depend on the information you share.

That does not make any sense. Why does it have to be a function for that purpose?

You don’t need a function when it’s about a scheduled task you want to run unattended. Just use a script. You can pass parameters to it just like you do with functions. :man_shrugging:t3:

This is the last version of the PowerShell Function , add to it a log for each moving file name:

function udfMoveFilesToAzurePRD {
    param (
        [string]$SourcePath = "E:\MSSQL\MSSQL13.RPRD01\MSSQL\Backup",
        [string]$DestinationPath = "Z:\DB\MSSQL\RWE\PRD",
        [string]$LogFolderPath = "C:\Scripts\Logs"
    )

    try {
        # Set the execution policy to Unrestricted within your script
        #Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process

        # Check if the specified source path exists
        if (-not (Test-Path -Path $SourcePath -PathType Container)) {
            Write-Host "Source path does not exist."
            return
        }

        # Check if the specified destination path exists, and create it if not
        if (-not (Test-Path -Path $DestinationPath -PathType Container)) {
            New-Item -Path $DestinationPath -ItemType Directory -Force | Out-Null
        }

        # Use RoboCopy to move files from source to destination with logging
        $RoboCopyArgs = "/MOV /MINAGE:8 /IS /LOG+:$LogFolderPath\MoveFilesPRDLog.txt /TEE"
        $RoboCopyCommand = "RoboCopy `"$SourcePath`" `"$DestinationPath`" *.* $RoboCopyArgs"

        Invoke-Expression -Command $RoboCopyCommand

        # Parse the RoboCopy log to extract the moved files
        $MovedFiles = Get-Content -Path "$LogFolderPath\MoveFilesPRDLog.txt" | Where-Object { $_ -match '^  (\S+) -> (\S+)' } | ForEach-Object {
            $Matches[1] | Out-Null
            $SourceFile = $Matches[1]
            $Matches[2] | Out-Null
            $DestinationFile = $Matches[2]
            [PSCustomObject]@{
                SourceFile = $SourceFile
                DestinationFile = $DestinationFile
            }
        }

        # Output the list of moved files
        if ($MovedFiles.Count -gt 0) {
            Write-Host "Moved Files:"
            $MovedFiles | ForEach-Object {
                Write-Host "Source: $($_.SourceFile) -> Destination: $($_.DestinationFile)"
                # Create a log file for each moved file
                $LogFile = Join-Path -Path $LogFolderPath -ChildPath "$($_.SourceFile.Replace('\', '_')).log"
                "Moved from $($SourcePath)\$($_.SourceFile) to $($DestinationPath)\$($_.DestinationFile)" | Out-File -FilePath $LogFile
            }
        }
    } catch {
        # Handle any errors
        $errorMessage = "Error: $_"
        Write-Error $errorMessage
        Write-Output "ERROR: $errorMessage"
    } finally {
        # Reset the execution policy to its previous value (optional)
        #Set-ExecutionPolicy -ExecutionPolicy Restricted -Scope Process
    }
}

Still no Errors But also not moving files!

Again … It will not work this way. The function never sees the parameter you pass to the script. You came here to ask for help but now you refuse to take the advice you get. Would it hurt you when you tried at least? :man_shrugging:t3:

Here you have some more to read about:

If you have issues passing parameters to the script you could use a settings file you place next to the script.

Here is the solution I usually use in those cases:

Sorry to react out frustration , but the main issue is that I want to understand the mechanism of Windows Scheduler !, and the reasons behind not giving any Errors and in the same time not doing what it suppose to do

Because the task scheduler does not experience any error. The script runs without error. Why should the task scheduler report an error when there is none.

The issue is in your script. If you’ve read the help topic I linked you could see that you even can use function but you need to “catch” the parameters from the script first before you can pass them to the function. But I wouldn’t recommend that because it adds unnecessary complexity.

I’d recommend to do a B I G step back and re-start with a very simple script from scratch. If that works as expected add functionality.

Again - passing parameters to scripts is sometimes error prone. You may use a config file placed next to the script. This way you don’t even need to change the task if you want to change the settings you defined in the config file.

1 Like

Can you show how?, is this config.json kind file?

So didn’t even bother looking at the link I shared in my answer before.
Please look at it. The explanation is in the ps1 file.

So, based on what you mentioned , let me do the BIG step and remove the function part of the function and save the rest of the code as the name of the function , then run the rest from windows scheduler , like this:

    param (
        [string]$SourcePath = "E:\MSSQL\MSSQL13.RACC01\MSSQL\Backup",
        [string]$DestinationPath = "Y:\DB\MSSQL\RWE\ACC",
        [string]$LogFolderPath = "C:\Scripts\Logs"
    )

    try {
        # Set the execution policy to Unrestricted within your script
        #Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process

        # Check if the specified source path exists
        if (-not (Test-Path -Path $SourcePath -PathType Container)) {
            Write-Host "Source path does not exist."
            return
        }

        # Check if the specified destination path exists, and create it if not
        if (-not (Test-Path -Path $DestinationPath -PathType Container)) {
            New-Item -Path $DestinationPath -ItemType Directory -Force | Out-Null
        }

        # Use RoboCopy to move files from source to destination with logging
        $RoboCopyArgs = "/MOV /MINAGE:8 /IS /LOG+:$LogFolderPath\MoveFilesPRDLog.txt /TEE"
        $RoboCopyCommand = "RoboCopy `"$SourcePath`" `"$DestinationPath`" *.* $RoboCopyArgs"

        Invoke-Expression -Command $RoboCopyCommand

        # Parse the RoboCopy log to extract the moved files
        $MovedFiles = Get-Content -Path "$LogFolderPath\MoveFilesACCLog.txt" | Where-Object { $_ -match '^  (\S+) -> (\S+)' } | ForEach-Object {
            $Matches[1] | Out-Null
            $SourceFile = $Matches[1]
            $Matches[2] | Out-Null
            $DestinationFile = $Matches[2]
            [PSCustomObject]@{
                SourceFile = $SourceFile
                DestinationFile = $DestinationFile
            }
        }

        # Output the list of moved files
        if ($MovedFiles.Count -gt 0) {
            Write-Host "Moved Files:"
            $MovedFiles | ForEach-Object {
                Write-Host "Source: $($_.SourceFile) -> Destination: $($_.DestinationFile)"
                # Create a log file for each moved file
                $LogFile = Join-Path -Path $LogFolderPath -ChildPath "$($_.SourceFile.Replace('\', '_')).log"
                "Moved from $($SourcePath)\$($_.SourceFile) to $($DestinationPath)\$($_.DestinationFile)" | Out-File -FilePath $LogFile
            }
        }
    } catch {
        # Handle any errors
        $errorMessage = "Error: $_"
        Write-Error $errorMessage
        Write-Output "ERROR: $errorMessage"
    } finally {
        # Reset the execution policy to its previous value (optional)
 
    }

And in the Action tab of the Task I will run “PowerShell” and the argument shall be:

-File  "udfShowFilesToCopyOnACC.ps1" -SourcePath "E:\MSSQL\MSSQL13.RACC01\MSSQL\Backup" -DestinationPath "Y:\DB\MSSQL\RWE\ACC" -LogFolderPath "C:\Scripts\2"

The Task is Running now , No Errors … No moving files!
Do you think I made something wrong?, if so can you correct me and give me the solution that can work

1 Like