SQL Server Job Powershell Failing: Troubleshooting Guide

powershellsql server

I have a SQL Server 2014 instance job that runs nightly that moves backups from the local drive to a network drive. I wrote it using PowerShell.

$mypath = $('F:\Backup\CRMDB01$CRMDB012014\Prod\Full\')
$mydestination = '\\stagingshare\fromprod\'
$myfile = gci $mypath | sort LastWriteTime | select -last 1
copy-item -path ($mypath + $myfile) -destination $mydestination

The job is failing with "reason: line(1): Syntax Error". I have it set to "Run as:" proxy_1.

proxy_1 has a credentialed name next to it that has access to the share. I don't have any principals selected.

What am I missing?

Best Answer

The syntax error you are getting is from the $() this is taken as a token in SQL Server Agent context...so it will always bark at that; removing it should fix the syntax error.

On the copy issue you will likely need to specify the property you want to pass into Copy-Item. Just passing $myfile is not going to work because that contains the full object of the output from gci. So change it to something like:

$myfile = gci $mypath | sort LastWriteTime | select -last 1 -ExpandPropety FullName
Copy-Item -path $myfile -destination $mydestination

Now $myFile will contain the full path to your last file:

Related Solutions

Copy Backup Files to Remote Share in SQL Server Agent Job Without AD/Domain Accounts

To answer the specific question of why it is failing is that the service account on your server does not have the correct permissions to access the share.

I have an environment where I have 2 servers not on a domain. Using SQL Server Agent proxies is the way I solved this.

I have a special account I use for the 2 machines (Your accounts with the same name and password) which I had to add to the instance level credentials (under security in SSMS Object explorer) (see MSDN article "Create a Credential" ) and add an agent proxy under the Operating System (CmdExec) .

Once those are created you can select the account to run as (the name you gave your proxy) and it will work. However this is on 2008 and I have not played with the 2012 agent that much.
Should still work the same though.

You would then use your robocopy line "robocopy d:\SQLBACKUP \10.195.xx.yyy\backup /S /purge /MAXAGE:7 /MT:1 /Z" as your command (I use xcopy but it works the same).

SQL Server Agent – Troubleshooting Powershell Script Errors

I believe your issue is going to be the SQLPS provider. Since PowerShell steps in SQL Server Agent automatically put you into the context of that provider some commands that work in your normal console will not function the same way. A write up was done here with Set-Location. You basically have to tell SQLPS the provider you want to use.

Your code would look something like below:

set-location -Path Microsoft.PowerShell.Core\FileSystem::"\\server\companydocuments\MyApp\Application Files\"

You could also wrap the remainder into two lines if you wanted to:


cd (Get-ChildItem | Sort-Object name -Descending | Select name -First 1).name
& ".\Application.exe"

Best Answer

Related Solutions

Copy Backup Files to Remote Share in SQL Server Agent Job Without AD/Domain Accounts

SQL Server Agent – Troubleshooting Powershell Script Errors

Related Question