Powershell syntax error in SQL server Agent job

  • 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 1 week, 1 day ago by  sdbjakeer.
  • Does drive E: exist on the SQL Server machine, or is it a mapped drive?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • E drive is local drive not an network mapped.

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply