CREATE PROCEDURE [dbo].[usp_Delete_Older_Files]
@FilesToKeep TINYINT = 1,
@Directory VARCHAR(500) = 'H:\Full\',
@FileDirExpression VARCHAR(500) = 'CoreStore_Full*.bak',
@FileLikeExpression VARCHAR(500) = 'CoreStore_Full%.bak'
AS -- =====================================================================================================================
-- Author: Gokhan Varol
-- Create date: July 2010
-- Description: This procedure takes an input Directory and file expression valid in dos prompt and file expression
-- valid in a sql statement and how many files to keep matching expressions and delete files in the
-- directory
-- =====================================================================================================================
SET NOCOUNT ON
DECLARE @FileName VARCHAR(1000),
@cmd VARCHAR(1000)
IF RIGHT(@Directory, 1) <> '\'
SET @Directory = @Directory + '\'
IF LEFT(@FileLikeExpression, 1) <> '%'
SET @FileLikeExpression = '%' + @FileLikeExpression
IF RIGHT(@FileLikeExpression, 1) <> '%'
SET @FileLikeExpression = @FileLikeExpression + '%'
DECLARE @TEMPFileDetail TABLE (FILE_DATE DATETIME,
FILE_SIZE BIGINT,
FileName VARCHAR(1000))
INSERT @TEMPFileDetail (FILE_DATE,
FILE_SIZE,
FileName)
SELECT di.LastWritten AS FILE_DATE,
SIZE AS FILE_SIZE,
name AS FileName
FROM FS.GetDirectoryInfo(@Directory, @FileDirExpression) di
WHERE name LIKE @FileLikeExpression
AND SIZE > 0 ;
WITH ToDelete
AS (SELECT TOP (@FilesToKeep) *
FROM @TEMPFileDetail
ORDER BY FILE_DATE DESC)
DELETE ToDelete ;
DELETE_LOOP:
SELECT TOP 1 @FileName = FileName
FROM @TEMPFileDetail
IF @@ROWCOUNT = 1
BEGIN
DELETE @TEMPFileDetail
WHERE FileName = @FileName
SET @cmd = 'EXEC master.dbo.XP_CMDSHELL ''DEL /q ' + @Directory + @FileName + ''', no_output'
PRINT @cmd
--master.dbo.XP_CMDSHELL @cmd, no_output
EXEC (@cmd)
GOTO DELETE_LOOP
END