0

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.

JonDBA5420
  • 97
  • 9

1 Answers1

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.

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"
Scott Hodgin - Retired
  • 24,062
  • 2
  • 29
  • 52