How can I format my string in SQL so that it is interpreted properly in Powershell?

I'm currently working on a simple SQL Script that I want to run from SSMS. What the script is supposed to do is take a database, make a backup of it, and then make a .zip file of that backup.

The problem I am running into is when attempting to zip up the backup. I declared all of my variables at the beginning of the file and I believe that when I attempt to execute @sqlcmd the string is not being read by Powershell properly.

    SET @sqlcmd = ('powershell.exe [IO.Compression.ZipFile]::CreateFromDirectory("' + @bkpath + '", "C:\'+ @fileName + '.zip")')
    PRINT @sqlcmd 
    -- this statement returns (powershell.exe [IO.Compression.ZipFile]::CreateFromDirectory("C:\Backup\", "C:\ScriptingTestDB_20170607.zip"))
    EXEC xp_cmdshell @sqlcmd

Running this code returns the following output in my results window:

At line:1 char:47
+ [IO.Compression.ZipFile]::CreateFromDirectory(C:\Backup", C:\Scriptin ...
+                                               ~
Missing ')' in method call.
At line:1 char:56
+ ... le]::CreateFromDirectory(C:\Backup", C:\ScriptingTestDB_20170607.zip)
+                                       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The string is missing the terminator: ".
At line:1 char:47
+ ... le]::CreateFromDirectory(C:\Backup", C:\ScriptingTestDB_20170607.zip)
+                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Unexpected token 'C:\Backup", C:\ScriptingTestDB_20170607.zip)' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingEndParenthesisInMethodCall
NULL

After some testing I think I figured out that the problem lies with how Powershell interprets the SQL string. Because of the many quotes It's possible that even though SQL prints out the string correctly, when Powershell gets ahold of it the format gets messed up so something needs to be changed there, but still not sure what. Does anyone know of any guidelines I can use to find out how the SQL code should be written such that Powershell reads the following:

[IO.Compression.ZipFile]::CreateFromDirectory("C:\Backup\", "C:\ScriptingTestDB_20170607.zip")

Solution

You need to escape "-characters, with \. My working script:

declare @sqlcmd varchar(1000), @bkpath nvarchar(max) = 'C:\i1\tmp', @filename nvarchar(max) = 'file'

SET @sqlcmd = 'powershell.exe Add-Type -AssemblyName System.IO.Compression.FileSystem; [System.IO.Compression.ZipFile]::CreateFromDirectory(\"' + @bkpath + '\", \"C:\i1\'+ @fileName + '.zip\")'
EXEC xp_cmdshell @sqlcmd