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

Question

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")

xp_cmdshell is a command prompt, it is taking the first quoteall the code that needs to get to PowerShell needs to be escaped from the cmd interpreter. You need it to be `powershell "[io..]::('c:\...', '...')"`` where the PS script is quoted for the cmd interpreter, and the paths are single quoted so they don't break the double quoting. So escape the single quotes in your SQL string. Then fix the way your Add-Type won't carry over from one process to the next. This kind of thing is why powershell.exe accepts a BASE64 encoded command, btw. — TessellatingHeckler, Jun 07 '17 at 22:22

score 2 · Accepted Answer · answered Jun 07 '17 at 22:30

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

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

1 Answers1