One cool thing that you can do in SQL Server that a lot of people don’t know about is run PowerShell code from a SQL Agent job. However, I recently ran into a problem when trying to schedule a SQL Agent job to run a PowerShell script. The script is fairly simple, it scans a local directory and removes any backup files over 7 days old.
|
1 2 3 4 5 6 7 |
$BackupDir = 'C:\MSSQL\Backup\Path' $RetentionInDays = '7' Get-ChildItem -Path $BackupDir -filter '*.bak' -Recurse -force | ` ? {$_.lastwritetime -lt (Get-Date).AddDays(- $($RetentionInDays)) -and -not ($_.psiscontainer)} | remove-item -force |
The script runs with no problems in PowerShell ISE on the server, however when trying to run the above code from a SQL Agent job, I get the following error.
Unable to start execution of step (reason: Syntax error). The step failed.
That’s not a very informative error. After quite a bit of digging, I came across this DBA Stack Exchange post that led me to the solution. As it turns out, you cannot use a PowerShell Sub-Expression Operator in a SQL Agent job, because SQL Sever sees the syntax of $($VarName) as a User Token. So the fix for me was to remove the $($RetentionInDays) variable and just hard code the value instead. Running the below code in my SQL Agent job executes without error.
|
1 2 3 4 5 6 |
$BackupDir = 'C:\MSSQL\Backup\Path' Get-ChildItem -Path $BackupDir -filter '*.bak' -Recurse -force | ` ? {$_.lastwritetime -lt (Get-Date).AddDays(-7) -and -not ($_.psiscontainer)} | remove-item -force |
Since I was only using this syntax for one variable, it was an easy fix for me. However, some people may have a lot of variables in their PowerShell script that they don’t want to hard code, which is understandable. In that case, I would recommend saving the PowerShell code off to a .ps1 file, then executing that from the SQL Agent. The above linked Stack Exchange post also shows how you can reset your variables so you don’t have to use the problematic syntax in your SQL Agent job.