SQL Server Job Powershell is failing

Question

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?

• user507: Take the $() out from around your first line, so should just be $myPath = 'F:\Backup\CRMDB01$CRMDB012014\Prod\Full\'
• Chris Whisenhunt: That was it. It now works, but for some reason doesn't copy the file.
• Jonathan Fite: Try adding a line that just calls c:\. I've found that the default provider is SQL when running PowerShell jobs from SQL Agent. I typically throw a line that calls just C:,円 to force the provider to switch to the file system. It's the least fragile method I've found for doing that.

Answer 1

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: enter image description here

• Chris Whisenhunt: This is awesome. It succeeds now in the SQL job, but still doesn't move the file. Thoughts?
• user507: Is there a file to move? Permissions? There are a handful of things to check on why the file is not going anywhere.
• Chris Whisenhunt: Yes the file exists. The proxy that it's using is linked to a windows user that has full writes to the share. Is there any where to look to see logs for a successful job?
• user507: Add an output file to the SQL Agent job will capture anything output by the script.
• Chris Whisenhunt: The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 4 in a PowerShell script. The corresponding line is 'copy-item -path $myfile -destination $mydestination'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Source and destination path did not resolve to the same provider.' Is this something that I need specify with the paths?