Scripting %ProgramFiles%\7-Zip\7z.exe
in a script as @Richard mentions was my first thought, but you said you're streaming your backup over the network.
This means you'd want to execute the compression on the file server, not the SQL server (where SQLAgent runs), otherwise you'll be streaming all that data back to %TEMP% on the SQL server in order to compress it, just to return the compressed bits to the file server.
If that's true, then your options are to execute 7z.exe
on the file server at the end of backup, perhaps by one of these methods:
psexec \\FILESERVER C:\7-Zip\7z.exe a todays-full-sql-backup.7z 200909201800_db.bak
or something like that. You need to define the name of the .bak file you want to compress somehow.
A scheduled task on the file server, set to run after the backup has had plenty of time to finish.
PowerShell 2 remoting, but this isn't anything I've tried - way too new.
Other ideas - why do you need a 7z archive? Is it a solution to compress the directory you're backing up to? Do you have space to back up locally on the sql server, 7zip the file and then send it over the network?
Edit: To determine the name of the backup file you can cheat quite a bit. You don't need to know the whole name. You can try something like "for each file named 2009MMDD*_MyDatabaseName_DB.bak, zip this file, copy it to \server\share, check for errors, delete the file".
Here's the barest bones of an UNTESTED shell script, something like,
set DB_NAME=MyDatabaseName
set BAK_DIR=<wherever your backup gets created>
set NET_SHARE=\\Server\Share
:GET_NAME_DATE_PART
rem #This depends entirely how Windows writes dates in your part of the world
rem #if I were you I'd probably get some utility (now.exe?) to extract yyyymmdd
rem #from today's date. On our locale %date% looks like 2009-09-20
set YYYY=%date:~0,4%
set MM=%date:~5,2%
set DD=%date:~8,2%
:ARCHIVE_BAK_FILE
pushd "%BAK_DIR%"
for %%A in (%YYYY%%MM%%DD%*_%DB_NAME%_DB.BAK) do (
"C:\Program Files\7-Zip\7z.exe" a %%A %%A.7z
move %%A.7z "%NET_SHARE%\%%A.7z"
rem #CHECK FOR ERRORS MAYBE?
del /q %%A
)
popd
The for
statement should only ever find one file per day to zip up and delete.
That funky %date:~0,4%
syntax is called variable substrings, if you haven't seen it before - it's in the help.
You might well prefer forfiles.exe
to for
.