I have a PowerShell job step I'm trying to execute and it's failing with a syntax error I believe is because I'm trying to escape double-quotes in my code. Can anyone take a look and help me with getting the escape characters right, or tweaking my code.

powershell.exe "Compress-Archive -Path \\NAS\DB-backups\BackupFiles -DestinationPath `"\\NAS\DB-Backups\BackupFiles $(get-date -f yyyy-MM-dd).zip`" -CompressionLevel Optimal"

I've also tried it using \ as the escape character

powershell.exe "Compress-Archive -Path \\NAS\DB-backups\BackupFiles -DestinationPath \"\\NAS\DB-Backups\BackupFiles $(get-date -f yyyy-MM-dd).zip\" -CompressionLevel Optimal"

I tried running this script (with \ escape characters) in the command line and it ran fine. Thanks again for any 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.

Referencing your attempts,

$(get-date -f yyyy-MM-dd).zip

notice the $( where you're trying to get the current date for the zip file. That syntax 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 ($ZipDate) first and then use that to finish the Powershell command.

Try this:

powershell.exe "$ZipDate = (get-date -f yyyy-MM-dd); Compress-Archive -Path "C:\TestCompress" -DestinationPath "c:\TestCompressOut\$ZipDate.zip" -CompressionLevel Optimal"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.