--http://www.7-zip.org/download.html
--http://downloads.sourceforge.net/sevenzip/7za920.zip

--xp_cmdshell is limited to ONE set of double quotes.
--one of the paths needs to not contain spaces!
--see 2003 MS KB http://support.microsoft.com/kb/266224
DECLARE @results TABLE(results varchar(255))
declare @command varchar(2000)
--zip one file
SET @command = 
    '"C:\DataFiles\7zip_CommandLine_7za465\' --path to 7za command line utility note the dbl quotes for long file names!
    + '7za.exe"'        --the exe: i'm using in the command line utility.
    + ' a '             --the Add command: add to zip file:
    + 'C:\DataFiles\'   --path for zip
    + 'myZipFile.zip'   --zip file name, note via xp_cmdshell only one pair of dbl quotes allowed  names!
    + ' '               --whitespace between zip file and file to add
    + 'C:\DataFiles\'   --path for the files to add
    + 'SandBox_2011-07-25.bak' --the file
     + ' -y'             --suppress any dialogs by answering yes to any and all prompts
print @command
--"C:\DataFiles\7zip_CommandLine_7za465\7za.exe" a C:\DataFiles\myZipFile.zip C:\DataFiles\SandBox_2011-07-25.bak -y

insert into @results
exec xp_cmdshell @command  

select * from @results 
/*
NULL
7-Zip (A) 4.65  Copyright (c) 1999-2009 Igor Pavlov  2009-02-03
NULL
Scanning
NULL
Updating archive C:\DataFiles\myZipFile.zip
NULL
Compressing  SandBox_2011-07-25.bak
NULL
Everything is Ok
NULL
*/

    Option Explicit

    ' Need to create a full path following the Target Location:
    ' Higher Level Folder: "YYYY-MMM_Backup"
    ' File Name "YY-MM-DD.Zip"
    ' Backing up the "CPAS folder and all its subfolders"

    Dim YYYY
    Dim YY
    Dim MMM
    Dim MM
    Dim DD

    Dim strZipInstruction
    Dim strZipProgLoc
    Dim strTargetLoc
    Dim strSourceLoc
    Dim FileNameZip
    Dim strDate
	
	Dim oFSO
	Set oFSO = CreateObject("Scripting.FileSystemObject")
	

    YYYY = Cstr(Year(Date))
    YY = Right(YYYY,2)
    MM = FormatNumber((Month(Date)),0,-1)
    MMM =  Left(MonthName(Month(Date)),3)
    DD = FormatNumber(Day(Date),0,-1)

	strTargetLoc = "H:\mtv\secure\Construction\Access\All Database Backup\" & YYYY & "-"   & MMM & "_Backup\"
	If Not oFSO.FolderExists(strTargetLoc) Then
		oFSO.CreateFolder strTargetLoc
	End If
	strTargetLoc = """H:\mtv\secure\Construction\Access\All Database Backup\" & YYYY & "-"   & MMM & "_Backup\"""
    strSourceLoc = "H:\mtv\secure\Construction\Access\CPAS\"
    strZipProgLoc = """C:\Documents and Settings\jamesshaffer\My Documents\7-Zip\7za.exe"" a "
    strDate = YY & "-" & MM & "-" & DD
    FileNameZip = strDate & ".zip"
    strZipInstruction=strZipProgLoc & strTargetLoc & FileNameZip & " " & strSourceLoc
	MsgBox strZipInstruction 

"C:\Documents and Settings\jamesshaffer\My Documents\7-Zip\7za.exe" a "F:\mtv\secure\Construction\Access\All Database Backup\2012-Oct_Backup\"12-10-23.zip F:\mtv\secure\Construction\Access\CPAS\

--"C:\Documents and Settings\jamesshaffer\My Documents\7-Zip\7za.exe" a "F:\mtv\secure\Construction\Access\All Database Backup\2012-Oct_Backup\12-10-23.zip" "F:\mtv\secure\Construction\Access\CPAS\"

    ' Need to create a full path following the Target Location:
    ' Higher Level Folder: "YYYY-MMM_Backup"
    ' File Name "YY-MM-DD.Zip"
    ' Backing up the "CPAS folder and all its subfolders"

    Dim YYYY
    Dim YY
    Dim MMM
    Dim MM
    Dim DD

    Dim strZipInstruction
    Dim strZipProgLoc
    Dim strTargetLoc
    Dim strSourceLoc
    Dim FileNameZip
    Dim strDate
    
    Dim oFSO
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    

    YYYY = CStr(Year(Date))
    YY = Right(YYYY, 2)
    MM = FormatNumber((Month(Date)), 0, -1)
    MMM = Left(MonthName(Month(Date)), 3)
    DD = FormatNumber(Day(Date), 0, -1)

    strTargetLoc = "M:\mtv\secure\Construction\Access\All Database Backup\" & YYYY & "-" & MMM & "_Backup\"
    If Not oFSO.FolderExists(strTargetLoc) Then
        'oFSO.CreateFolder strTargetLoc
    End If
    strTargetLoc = """M:\mtv\secure\Construction\Access\All Database Backup\" & YYYY & "-" & MMM & "_Backup\"
    strSourceLoc = """M:\mtv\secure\Construction\Access\CPAS\"""
    strZipProgLoc = """C:\Documents and Settings\jamesshaffer\My Documents\7-Zip\7za.exe"" a "
    strDate = YY & "-" & MM & "-" & DD
    FileNameZip = strDate & ".zip"""
    strZipInstruction = strZipProgLoc & strTargetLoc & FileNameZip & " " & strSourceLoc
    'Debug.Print strZipInstruction
    MsgBox strZipInstruction