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?

  • 1
    Take the $() out from around your first line, so should just be $myPath = 'F:\Backup\CRMDB01$CRMDB012014\Prod\Full\' – Shawn Melton Jan 5 '16 at 19:42
  • That was it. It now works, but for some reason doesn't copy the file. – Chris Whisenhunt Jan 5 '16 at 20:01
  • 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. – Jonathan Fite Jan 5 '16 at 20:04
up vote 2 down vote accepted

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

  • This is awesome. It succeeds now in the SQL job, but still doesn't move the file. Thoughts? – Chris Whisenhunt Jan 5 '16 at 20:14
  • Is there a file to move? Permissions? There are a handful of things to check on why the file is not going anywhere. – Shawn Melton Jan 5 '16 at 20:18
  • 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? – Chris Whisenhunt Jan 5 '16 at 20:21
  • Add an output file to the SQL Agent job will capture anything output by the script. – Shawn Melton Jan 5 '16 at 20:22
  • 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? – Chris Whisenhunt Jan 5 '16 at 20:34

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.