Deleting Old Backup Filees

  • Because we recently got a new SQL Backup utility, I am doing a lot of my db backups using TSQL instead of Maintenance Plans. 

    Does anyone know a way to easily delete backup files older than a week with T-SQL?

    The maint. plans do it by adding a switch to the xq_sqlmaint procedure that get called, but I can't get that procedure to run when all I ask it to do is delete old backups.

    I'd like to do something like:

    exec xp_sqlmaint '-D dktemp -DelBkUps "5days"'

    But when I do that it gives me:

    (28 row(s) affected)

    Server: Msg 22029, Level 16, State 1, Line 0

    sqlmaint.exe failed.

    Any suggestions?

    Thanks,

    Jason

    The Redneck DBA

  • Try this...

    CREATE

    PROCEDURE dbo.usp_dropOldBackups

    (

    @prmLife INT = 0 -- number of days after which device gets dropped

    )

    AS

    BEGIN

    /*

    2006.06.18 ATL - this code ripped by author from usp_dbMaint8 for public domain use.

    */

    SET NOCOUNT ON

    DECLARE

    @bdName sysname, -- backup device name

    @errNum INT, -- for @@ERROR when applicable

    @expDate DATETIME -- backup device expiration date

    IF @prmLife > 0

    BEGIN

    -- backup device list

    DECLARE @bdNames TABLE

    (

    bdName sysname NOT NULL

    )

    SET @expDate = DATEADD(day, -@prmLife, GETDATE()) -- set 'expiration' date

    -- get a list of all the devices that have expired.

    INSERT INTO @bdNames

    SELECT sd.name

    FROM master..sysdevices sd

    INNER JOIN msdb..backupmediafamily bmf ON sd.name = bmf.logical_device_name

    INNER JOIN msdb..backupset bs ON bmf.media_set_id = bs.media_set_id

    WHERE

    sd.status & 16 = 16 AND

    sd.cntrltype = 2 AND

    bs.backup_finish_date < @expDate

    SELECT TOP 1 @bdName = bdName

    FROM @bdNames

    ORDER BY bdName

    WHILE @@ROWCOUNT > 0

    BEGIN

    EXEC @errNum = sp_dropdevice @logicalname = @bdName, @delfile = 'delfile'

    IF @errNum = 0

    BEGIN

    SELECT 'Complete - Backup Device (' + @bdName + ') was successfully dropped'

    END

    ELSE

    BEGIN

    SELECT 'ERROR - Backup Device (' + @bdName + ') drop failed'

    END

    -- ENDIF @errNum = 0

    DELETE FROM @bdNames

    WHERE bdName = @bdName

    SELECT TOP 1 @bdName = bdName

    FROM @bdNames

    ORDER BY bdName

    END

    -- ENDWHILE @@ROWCOUNT > 0

    END

    --ENDIF @prmLife <> 0

    RETURN

    END

    GO

    ----------------------------

    <= as with anything; no warrantees written or implied...=>  but I have been using a more elaborate version of the above for many years.  One thing to be careful of if you don't have the older backups on tape or elsewhere:  remember that you must restore starting with a FULL or FILEGROUP backup(s). 

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply