Archive backups with a PowerShell script launched by SQL Server Agent I would like to run a Powershell script via the SQL Server Agent.
This script works fine if I run it manually through the command prompt, but the job history in SQL Server Management Studio tells me a syntax error on line 8.

Is there a difference in the SQL Server Agent runtime environment?

Here is my script:

$ArchivesMPath = "E:\ArchivesBDD\Mensuelles"
$SauvegardeMPath = "E:\SauvegardeBDD\Mensuelle"
$ArchivesQPath = "E:\ArchivesBDD\Quotidiennes"
$SauvegardeQPath = "E:\SauvegardeBDD\Quotidienne"
$DAYSOFWEEK = "Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"
$MONTHS = "Decembre", "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre"

$DAILYDIR = "$ArchivesQPath\$($DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__])"
if ([System.IO.Directory]::Exists($DAILYDIR)){
    Remove-Item $DAILYDIR -Force -Recurse
}
New-Item -Path $DAILYDIR -ItemType directory
Move-Item -Path "$SauvegardeQPath\*" -Destination $DAILYDIR

if ((get-date).AddDays(-1).day -eq 1){
    $MONTHLYDIR = "$ArchivesMPath\$($MONTHS[(get-date).month])"
    if ([System.IO.Directory]::Exists($MONTHLYDIR)){
        Remove-Item $MONTHLYDIR -Force -Recurse
    }
    New-Item -Path $MONTHLYDIR -ItemType directory
    Move-Item -Path "$SauvegardeMPath\*" -Destination $MONTHLYDIR
}




Thank you in advance for your help
I believe the problem you're having has to do with SQL Server Agent Tokens as referred to by this Stack Exchange post SQL Agent - PowerShell step “syntax error”

Excerpting from that post


  This is a not very intuitive and I was never able to find anything
  concrete on the explanation [e.g. no exact BOL or white paper was
  found]. The syntax error in the SQL Agent job is a T-SQL syntax error
  based on User Tokens. So that basically means that a PowerShell
  Sub-Expression Operator is treated as a Token to SQL Server Agent. So
  in PowerShell this $( ) appears to be treated as reserved character
  sequence for SQL Server Agent.




In your example, the problem line is this

$DAILYDIR = "$ArchivesQPath\$($DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__])"


Notice the $( which is a Powershell sub-expression.  This part is attempting to be interpreted by the SQL Server Agent as a token.

My suggestion would be to separate out the sub-expression into another Powershell variable ($DAILYDIRDAY) and then use that to finish building the $DAILYDIR variable.

Give this a try:

$ArchivesMPath = "E:\ArchivesBDD\Mensuelles"
$SauvegardeMPath = "E:\SauvegardeBDD\Mensuelle"
$ArchivesQPath = "E:\ArchivesBDD\Quotidiennes"
$SauvegardeQPath = "E:\SauvegardeBDD\Quotidienne"
$DAYSOFWEEK = "Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"
$MONTHS = "Decembre", "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre"
$DAILYDIRDAY = $DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__]
$DAILYDIR = "$ArchivesQPath\$DAILYDIRDAY"


and follow up with the rest of the Powershell script