i've got a pretty simple maintenance plan that does a full backup of my db to a network'd location. works perfectly.

I'm wanting to 7zip the backup file AFTER the backup was done.

Is there a way i can do this in a maintenance plan? maybe using powershell?


i don't have os access to the network drive. just think of it as another hard disk, not a server. it's technically my mapped Z:\ drive.

  • I know how I'd attack it under Linux :)
    – warren
    Sep 20, 2009 at 9:19
  • @warren, how do you know the network location isn't Linux?
    – nray
    Sep 20, 2009 at 11:21
  • @nray - I don't, but since he says he doesn't have OS access to the share, I'm presuming that my solution wouldn't work :)
    – warren
    Sep 20, 2009 at 15:12
  • I don't see how saying that you know how to attack it under Linux contributes anything to the question, really. Sep 20, 2009 at 16:05
  • @Pure.Krome - nray's example script is just the normal Windows shell, CMD.EXE, you might call it a batch file or a command script or the DOS prompt.
    – nray
    Oct 1, 2009 at 13:50

3 Answers 3


A SQL Agent job can execute cmd.exe scripts.

A maintenance plan can execute SQL Agent jobs.

(Just create the agent job without a schedule.)


Combining what Richard and nray have said, initiate a 7zip-based archiving script at the end of the SQL backup (via an SQL Agent job).

You can use the follow CMD/batch file as a reference or even adapt it as necessary (it's opensource - and blame any bugs on me ;)

Ziparcy is a WINNT shell script that can be scheduled to create/update an encrypted zip/archive of your files (and directory structure).

This script leverages two other free utilities: 7-zip and wget.


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

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%

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"
    del /q %%A

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.

  • Yep. I can definately backup the solution to my local sql drive. i have '3' disks on the server. OS (raid 1), log file (raid 1), db (raid 5). so i can throw the backup on a temp location in the OS drive, 7z, then move it to the mapped network drive (ie. Z:\Backup). With that idea, how could I achieve this? How do i know the name of the backup (it's derived from the date, like u noted, above).
    – Pure.Krome
    Sep 20, 2009 at 13:20
  • You can query the msdn database to get the name of the file that you backed up to. Look in the tables which start with backup% in the table name. Then use this filename to dynamically create the 7zip command to run.
    – mrdenny
    Sep 20, 2009 at 20:02
  • @Pure.Krome - You only need to know as of the file name as to make it unique, not the hour-minutes bit. You know the date, and the pattern SQL Server uses to generate names.
    – nray
    Sep 20, 2009 at 22:17
  • @Nray, what scripting language is this in the example?
    – Pure.Krome
    Oct 1, 2009 at 0:53
  • @Pure.Krome - This is just the normal Windows shell, CMD.EXE.
    – nray
    Jan 8, 2010 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.