Post reply

Cleanup old backup files

  • April 7, 2007 at 5:34 pm

    #117992

    Hello DBA folks,

    A database backup job is created using T-sql script (BACKUP DATABASE DB TO DISK = 'XXXX.BKP') and it creates one full backup everyday on local server. I would like to delete/clean up old database backp copies using T-SQL or DTS packages or SSIS packages. Any help is greatly appreciated. Database maintenance plans shouldn't be used in my environment.

     

    Thanks,

    Bhushan

  • April 8, 2007 at 3:27 pm

    #699239

    You can make use of xp_delete_file  or xp_cmdshell...

    xp_delete_file  procedure is used in maintenace plans to delete old backups...

    DECLARE

    @ThreeDays VARCHAR(255)

    SELECT

    @ThreeDays = CAST(DATEADD(d, -3, GETDATE()) AS VARCHAR)

    EXECUTE

    master.dbo.xp_delete_file 0,N'c:\',N'bak',@ThreeDays

    MohammedU
    Microsoft SQL Server MVP

  • April 8, 2007 at 4:42 pm

    #699250

    You can also use the FileSystemObject in DTS (ActiveX) task to do this.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 2, 2007 at 6:10 pm

    #703974

    How do I add the Archive attribute to the delete statement to delete only those files that are 3 days or older and has already been backed up to tape?

    I don't have the xp_delete_file sp. Can you give me the sample code using xp_comshell to accomplish the above?

    Thank you very much in advance!

    Mickeytech

  • May 4, 2007 at 10:19 am

    #704415

    perhaps something along the lines of:

    (rough draft, not tested)

     create Table #backs ( ID INT IDENTITY, baks sysname NULL)

     DECLARE @back sysname, @i int, @cmd varchar(4000)

     SELECT @back = '', @i = 0

     SELECT @cmd = 'dir /b /O-D /A-A path\backupfiles*'

         RAISERROR(@cmd,10,1) WITH NOWAIT

     WAITFOR DELAY '00:00:02'

     INSERT #backs EXEC master..xp_cmdshell @cmd

     delete from #backs WHERE baks IS NULL

     --select * from #backs

     WHILE @i < (SELECT MAX(ID) FROM #backs) BEGIN

        SELECT @i = MIN(ID) FROM #backs WHERE ID > @i

        SELECT @back =  baks FROM #backs WHERE ID = @i

        SELECT @cmd = 'delete '@pfad+@back

        EXEC master..xp_cmdshell @cmd

    END

     DROP Table #backs

    regards

    karl

    Best regards
    karl

Viewing 5 posts - 1 through 4 (of 4 total)

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