Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002

    Unanswered: 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 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?


  2. #2
    Join Date
    Oct 2002

    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:

    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:

    DROP 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'
    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.
    Last edited by DBA; 11-30-02 at 00:40.

  3. #3
    Join Date
    Feb 2002
    How is the currently developed maintenance plan programmed - through the maintenance wizard / dts / stored procedure ... ? Which compression software package are you using ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts