Pages

Tuesday, July 6, 2010

SQL Query to Delete the OLD Back up files based on No of Days

Here is a small SQL Script developed to delete the OLD Backup files based on the Date.
All you need to do is just check how many days old files should be deleted and provide the same in
Select statement after get date.

SET NOCOUNT ON
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
PRINT 'Running reconfigure'
RECONFIGURE
GO
DECLARE @rc INT
DECLARE @cmd VARCHAR(1000)
DECLARE @cursor CURSOR
DECLARE @physicaldevicename VARCHAR(1000)
DECLARE @Err VARCHAR(1000)
SET @cursor = CURSOR FOR SELECT b.physical_device_name AS physicaldevicename FROM msdb..backupset AS a
 inner join msdb..backupmediafamily as  b on a.media_set_id = b.media_set_id
 WHERE a.backup_start_date 
 < GETDATE()-5 ORDER BY a.backup_start_date,a.database_name

OPEN @cursor
WHILE 1=1
BEGIN
    FETCH FROM @cursor INTO @physicaldevicename
    IF @@fetch_status <> 0
            BEGIN
                  SET @Err = @@fetch_status
                  PRINT @Err
                  BREAK
            END
    ELSE
    BEGIN
        SET @cmd = 'del ' + @physicaldevicename
            PRINT 'Starting deleting the backup file ' + @cmd
            EXEC @rc = master.dbo.xp_cmdshell @cmd
            IF @rc <> 0
                  BEGIN
                        PRINT 'Backup File ' + @physicaldevicename + ' was not deleted'
                  END
            ELSE
                  BEGIN
                        PRINT 'Backup File ' + @physicaldevicename + ' deleted successfully at timestamp '+ CONVERT(VARCHAR,GETDATE(),109)
                  END
      END
END
CLOSE @cursor
DEALLOCATE @cursor
GO
sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
PRINT 'Running reconfigure'
RECONFIGURE
GO
SET NOCOUNT OFF

No comments:

Post a Comment