Powershell syntax error in SQL server Agent job

• sdbjakeer

  Old Hand

  Points: 331

  More actions

  January 12, 2021 at 10:11 am

  #3831394

  Hi,

  I am running below code, running fine in PS terminal and ISE.  When I am running it from SQL agent job it's failing with

  "Message - Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed."

  ---------------------------------------------------------------------

  #Create folder

  $f = New-Item "E:\Archive_backups\$(get-date -f yyyy-MM-dd-hh-mm-ss)" -ItemType Directory -Force

  #Copy all files

  Copy-Item E:\MainBackups\* $f.FullName

  ------------------------------------

  Please suggest.

  • This topic was modified 4 years, 6 months ago by sdbjakeer.

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  January 12, 2021 at 11:12 am

  #3831409

  Does drive E: exist on the SQL Server machine, or is it a mapped drive?

  ---

• sdbjakeer

  Old Hand

  Points: 331

  More actions

  January 12, 2021 at 1:12 pm

  #3831427

  E drive is local drive not an network mapped.

• Jeffrey Williams

  SSC Guru

  Points: 90349

  More actions

  January 12, 2021 at 5:03 pm

  #3831548

  As stated in the other thread you posted to - the problem is that SQL Server Agent uses tokens, and tokens are defined by $(token).  Since your code has this: $(get-date -f yyyy-MM-dd-hh-mm-ss), SQL Server Agent is attempting to parse that out as a token.

  Change your code so it doesn't use that structure, something like:

  $directoryName = "E:\Archive_Backups\" + (Get-Date -f yyyy-MM-dd-hh-mm-ss);
  $f = New-Item $directoryName -ItemType Directory -Force;

  With that said - you can simplify your code to just this:

  Copy-Item -Path E:\MainBackups\ -Destination "E:\Archive_Backups\" + (Get-Date -f yyyy-MM-dd-hh-mm-ss);

   

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs