DECLARE @Path VARCHAR(100) -- location of the files
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT

SET @Path = 'G:\MSSQL\BACKUP\'

-- Stores the name of the file to be deleted
DECLARE @WhichFile VARCHAR(1000)

CREATE TABLE #DeleteOldFiles
(
DirInfo VARCHAR(7000)
)

-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @Path + ' /OD'

-- Run the dir command and put the results into a temp table
INSERT INTO #DeleteOldFiles
EXEC master.dbo.xp_cmdshell @cmd

-- Delete all rows from the temp table except the ones that correspond to the files to be deleted
DELETE 
FROM #DeleteOldFiles
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - 2

-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
FROM #DeleteOldFiles
	
SET @RowCnt = @@ROWCOUNT
		
-- Interate through the temp table until there are no more files to delete
WHILE @RowCnt <> 0
BEGIN
		
	-- Build the del command
	SELECT @cmd = 'del ' + @Path + @WhichFile + ' /Q /F'
		
	-- Delete the file
	EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
			
	-- To move to the next file, the current file name needs to be deleted from the temp table
	DELETE
	FROM #DeleteOldFiles
	WHERE SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))  = @WhichFile

	-- Get the file name portion of the row that corresponds to the file to be deleted
	SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
	FROM #DeleteOldFiles
		
	SET @RowCnt = @@ROWCOUNT
		
END
		
DROP TABLE #DeleteOldFiles