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?

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

1 Answers1

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

  • This is awesome. It succeeds now in the SQL job, but still doesn't move the file. Thoughts? – Chris Whisenhunt Jan 05 '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. –  Jan 05 '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 05 '16 at 20:21
  • Add an output file to the SQL Agent job will capture anything output by the script. –  Jan 05 '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 05 '16 at 20:34
  • So I added this to the front of the network share: Microsoft.PowerShell.Core\FileSystem::

    And am now getting an access is denied to the path. How should the credentials be setup for this? proxy_1 is mapped to a user account that has access to the path.

     – Chris Whisenhunt Jan 05 '16 at 20:41
  • Ah, yeah I brought up using that in this answer. The permissions for that proxy will need full permissions to the destination (both the share and folder itself), or the source based on what your error shows. –  Jan 05 '16 at 20:44
  • Ah ha! The user account didn't have access to the share, just the folder. It now works, beautifully. Thanks! – Chris Whisenhunt Jan 05 '16 at 20:47
  • So for some reason this executes every night, but it doesn't actually move the file. The output file is blank, so I told it to log everything to the history. No errors are thrown there. If I start the job at the file moving step it works fine. Thoughts? – Chris Whisenhunt Jan 12 '16 at 13:43