Cleanup old backup files

• Bhushan Kalla

  Default port

  Points: 1475

  More actions

  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

• MohammedU

  SSCoach

  Points: 19644

  More actions

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736198

  More actions

  April 8, 2007 at 4:42 pm

  #699250

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

• mickeytech2000

  Mr or Mrs. 500

  Points: 556

  More actions

  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

• Karl Klingler

  SSCertifiable

  Points: 5869

  More actions

  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