CREATE PROC [dbo].[isp_Backup_Delete]
AS
SET NOCOUNT ON

DECLARE @now datetime, @rowCnt int, @bkpRetention int, @i int
DECLARE @bkpLocation varchar(512), @cmd nvarchar(4000), @file varchar(256)

SELECT @now = GETDATE(), @i = 1

SELECT IDENTITY(int, 1, 1) AS dbInfoID, BackupRetention, BackupLocation
INTO #dbInfo
FROM DatabaseInfo
WHERE ServerName = @@SERVERNAME
ORDER BY DatabaseName

SET @i = @@ROWCOUNT

CREATE TABLE #Dir(DirInfo VARCHAR(7000))

WHILE @i <> 0
BEGIN
	SELECT @bkpRetention = BackupRetention, @bkpLocation = BackupLocation
	FROM #dbInfo
	WHERE dbInfoID = @i

	SET @cmd = 'dir ' + @bkpLocation + ' /OD'

	IF RIGHT(@bkpLocation, 1) <> '\'
		SET @bkpLocation = @bkpLocation + '\'

	INSERT INTO #Dir
	EXEC master.dbo.xp_cmdshell @cmd

	DELETE 
	FROM #Dir
	WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @bkpRetention

	SELECT TOP 1 @file = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
	FROM #Dir

	SET @rowCnt = @@ROWCOUNT

	WHILE @rowCnt <> 0
	BEGIN
		SET @cmd = 'del ' + @bkpLocation + @file + ' /Q /F'

		EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
			
		DELETE
		FROM #Dir
		WHERE SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @file

		SELECT TOP 1 @file = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
		FROM #Dir
		
		SET @RowCnt = @@ROWCOUNT
	END

	DELETE FROM #Dir

	SET @i = @i - 1
END


DROP TABLE #dbInfo, #Dir