4

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?

Improve this question
3
  • 1
    Take the $() out from around your first line, so should just be $myPath = 'F:\Backup\CRMDB01$CRMDB012014\Prod\Full\'
    – user507
    Commented Jan 5, 2016 at 19:42
  • That was it. It now works, but for some reason doesn't copy the file.
    – Chris Whisenhunt
    Commented Jan 5, 2016 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
    Commented Jan 5, 2016 at 20:04

1 Answer 1

Reset to default
2

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

Improve this answer
9
  • This is awesome. It succeeds now in the SQL job, but still doesn't move the file. Thoughts?
    – Chris Whisenhunt
    Commented Jan 5, 2016 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.
    – user507
    Commented Jan 5, 2016 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
    Commented Jan 5, 2016 at 20:21
  • Add an output file to the SQL Agent job will capture anything output by the script.
    – user507
    Commented Jan 5, 2016 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
    Commented Jan 5, 2016 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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