Archive backups with a PowerShell script launched by SQL Server Agent

Question

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

Answer 1

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