Error using PowerShell script for restore job

• tt-615680

  SSCertifiable

  Points: 7835

  More actions

  September 29, 2015 at 8:26 am

  #305105

  Dear All,

  I'm trying to create a restore job for restore job in sql using PowerShell but I get the following error, would anyone let me know what I'm doing wrong please?

  The job script encountered the following errors. These errors did not stop the script:

  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Set-location "filelocation"'.

  Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path 'filelocation' because it does not exist.

  ' A job step received an error at line 3 in a PowerShell script. The corresponding line is 'get-childitem -Filter '*.trn' | Select FullName, LastWriteTime | sort LastWriteTime -Descending | ft -AutoSize'.

  Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot call method. The provider does not support the use of filters. '. Process Exit Code -1. The step failed.

  Thank you on advance!

• WilburBud

  SSCrazy

  Points: 2914

  More actions

  September 29, 2015 at 11:24 am

  #1830233

  These errors did not stop the script

  You might want to set $ErrorActionPreference in the script or use –ErrorAction on the commandlet

  http://blogs.technet.com/b/heyscriptingguy/archive/2010/03/09/hey-scripting-guy-march-9-2010.aspx

  Cannot find path 'filelocation' because it does not exist

  In my environment, the SQL Server Agent runs as a domain login (example MyDomain\svc-sql), I have found that a powershell step in a SQL Server job can have permission problem with folders, including the default backup folder. I find that I need to explicitly set the folder permission to allow the service account read/write/modify as needed (right click folder, properties, security...)

  It appears that when the Agent makes the backup file with a job step running T-SQL it runs as MSSQLSERVER but when it uses powershell step to access the file it only has the MyDomain\svc-sql permissions.

  Cannot call method

  I think this is result of previous error (failure of the 'Set-location')