automatically compress backups

I have a maintenance plan for handling backups. I need to be able to amend the jobs so that the latest backup can be compressed (e.g. command line compression utility) before it is copied off across the network to a DR machine.

The issue is that the Maintenance Plan produces data/time specific backup file names - mulitiple backups stored in the same directory. Therefore, within a job step, I'm finding it difficult to code it such that only the latest backup is compressed. The command line syntax of these compression utilities is somewhat limited...

e.g. wzzip zipname.zip dbbackup_*.bak

nb. The '*' represents the wild card for the date/time part of the backup files created by the maintenance plan.

The problem is that the above command would keep adding multiple backups to the same zip archive. What I want is to simply add only the latest backup to the archive but to achieve this I would have to know the name of the backup file (created by the maintenance plan) programatically within the job.

I would have thought this was a fairly common requirement/problem. Has anyone got some suggestions/solutions?

CliveRE:
Q1 Has anyone got some suggestions/solutions?

A1 I doubt you'll like hearing this, my suggestion is don't compress production backups if you can possibly avoid it in any production setting (especially certain kinds of software based compression).

I've seen too many failed validation restores that seem to have only the fact of having been compressed and decompressed in common to heavily rely on any compression of backup dumps. (Granted, it does work 'most' of the time, which is generally fine for many development environments. However, in some situations where compression is implemented with larger backups I've seen routine daily restore validations fail or restore with problems every few weeks.)
---

If you are intent on going ahead anyway, what Sql Server version you are running is important. (In 2k installs I've implemented UDF functions instead of stored procedures for extracting dump information.)

Unfortunately no version has provided particularly rich built in backup / maintenence procedures to address the information needs you would require. (There are lots of undocumented ones that are worth looking at, but they are as subject to unannounced changes as the underlying MSDB table structures. I think you may be stuck implementing and maintaining your own special functions / stored procedures). Current Maintenance Plan Procedures (ver 7 / 2k) include:
sp_add_maintenance_plan
sp_add_maintenance_plan_db
sp_add_maintenance_plan_job
sp_delete_maintenance_plan
sp_delete_maintenance_plan_db
sp_delete_maintenance_plan_job
sp_help_maintenance_plan

The following user special stored procedure (implemented on some ver. 7 installs) returns the last DB or TL dumpfor a specified DB and Full or Log Dump on 7.0 and 2k (but not 6.x) installs:

Use
Master
Go
DROP PROCEDURE sp_LastDump
go
CREATE PROCEDURE sp_LastDump
--Fully Qualified Select most recent DBDump or TLDump
--@.pBkpSetTyp = I, D, L, @.pDBNam = DB name,
-- sp parameters:
@.pDBNam VarChar(512) = 'Master',
@.pBkpSetTyp VarChar(50) = 'D'
AS
exec ('Set NoCount On')
SELECT MsDb..backupmediafamily.physical_device_name
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE MsDb..backupset.backup_finish_date =
(SELECT MAX(MsDb..backupset.backup_finish_date)
AS Mxbackup_finish_date
FROM MsDb..backupmediafamily INNER JOIN
MsDb..backupset ON
MsDb..backupmediafamily.media_set_id = MsDb..backupset.media_set_id
WHERE (MsDb..backupset.database_name = @.pDBNam) AND
(MsDb..backupset.type = @.pBkpSetTyp))

Note: If running 6.x or earlier, you'll need to create a proc referencing 6.x msdb tables instead e.g.(sysbackuphistory sysbackupdetail). If later upgrade to 7.0 or 2k, or later versions, you'll need to rdefine any special stored procs you create to reflect newer MSDB tables.|||How is the currently developed maintenance plan programmed - through the maintenance wizard / dts / stored procedure ... ? Which compression software package are you using ?