Powershell - Copy .bak files

Copy .bak files from Local drive in SQL server to Central share

If you like command line scripting, you will like powershell. We have .bak files located on a backup drive on SQL server. They need to be copied to central share location for disaster recovery. I created the following powershell code. It is scheduled in SQL Agent. One tricky part is if  SQL job agent does not throw errors due to the central share does not exist or files do not not exit. So, we need to add error handling to let us know if the job fails.

cd c:
$originalpath = "S:\SQL\backup\"
$Destinationpath = "\\backups\bak\"
$filespath = "$Destinationpath\backup"
if (!(test-path $filespath))
{
    
    Write-Error "File Path error" -ea Stop
}

if(test-path $filespath)
{
    Remove-item $filespath -recurse -ea STOP
    Try
    {
        Copy-Item -Path $originalpath -filter *.bak -Destination $Destinationpath -force -Recurse -ea STOP -Errorvariable myError
    }
    Catch
    {
        Write-Error "Job Failure with $myError" -ea Stop
    }
   
}