Delete old backup files

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE FUNCTION udf_RightToken 
(@String varchar(1000), @Delimiter varchar(10))
RETURNS varchar(100)
AS
BEGIN
declare @LastSpace int
declare @TokenLength int
declare @Token varchar(100)
select @LastSpace = dbo.udf_LastPosInString(@Delimiter, @String)
set @TokenLength = LEN(@String)-@LastSpace
Select @Token = RIGHT(@String, @TokenLength)

RETURN @Token
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

----------------------------------------------------------
----------------------------------------------------------


CREATE FUNCTION udf_LastPosInString 
(@pattern varchar(10), 
 @string varchar(1000))
RETURNS int
AS
BEGIN
declare @start int, @position int
set @position = charindex(@pattern, @string)
set @start = @position
while @start <> 0
begin
   set @start = charindex(@pattern, @string, @start+1)
   If @start <> 0
set @position = @start
end
RETURN @position
--eg.
--DECLARE @sum AS int
--SELECT @sum = @p1 + @P2
--RETURN @sum
END

GO
----------------------------------------------------------
----------------------------------------------------------

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE    PROCEDURE usp_RemoveOldBackupFiles (
@Path varchar(250), 
@DeleteBeforeDate datetime,
@Extension varchar(4) = '.bak')
AS
-- Deletes all tran log and database backup files but most recent full backup file.
-- Parameters:
-- @Path - UNC path to backup directory 
-- i.e. \\<servername>\<path to backup directory>-- 
-- @Extension - extension of file being deleted
-- i.e. '.bak', '.trn', If blank, defaults to all files in directory
--older than the latest backup.
-- 
-- @DeleteBeforeDate - Deletes files older than this date.  If blank then
-- it deletes everything before the most recent '.bak' file.
-- i.e. @DeleteBeforeDate = 8/27/2003.  All files of type @Extension
-- that are located at @Path and are dated before but not 
-- including 8/27/2003 will be deleted.
-- uses udf_LastPosInString and udf_RightToken.
-- 
-- Declaration --
--declare @LeaveMostRecent bit
--declare @DeleteBeforeDate datetime
--declare @Extension varchar(4)
--declare @Path varchar(250), 
DECLARE @CommandLine varchar(100)
DECLARE @SQL VARCHAR(500), @FName VARCHAR(40), @Error INT
DECLARE @Delete VARCHAR(300), @Msg VARCHAR(100), @Return INT
declare @PosLastSpace int, @MostRecentFDate datetime

--Temp Set variables --
--SET @Path = '\\witibmct\D$\CTI_Backups\CTLSQLCL\master\'

--Trim the extension to three characters or default it to 'bak'.
If @Extension is null
set @Extension = 'bak'
else
set @Extension = right(@extension,3)

If Right(@Path, 1) <> '\'
set @Path = @Path+'\'
--select @Path as path
-- I dunno --
SET DATEFORMAT MDY

-- Drop tables if they exist --
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
    DROP TABLE #dirlist
IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
    DROP TABLE #Errors

-- Create Tables --
CREATE TABLE #dirlist (FEntry VARCHAR(1000), FDate VARCHAR(25), FName VARCHAR(50))

CREATE TABLE #Errors (Results VARCHAR(1000))

-- Set CommandLine for retrieving dir cmd --
set @CommandLine = 'dir /OD '+@Path+'*.'+@Extension

--select @CommandLine as commandline

--print @CommandLine
--  Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FEntry)
        exec master..xp_cmdshell @CommandLine
-- select * from #dirlist 
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
    SET @Msg = 'Error while getting the filenames with DIR ' 
    GOTO On_Error
END



--  Remove the garbage rows --
DELETE #dirlist WHERE 
isdate(SUBSTRING(FEntry,1,10)) <> 1 or
FEntry IS NULL 

--update #dirlist set FDate to the file date and FName to the file name --
update #dirlist 
set FName = dbo.udf_RightToken(FEntry, ' '),
    FDate = SUBSTRING(FEntry,1,10)

-- Set @DeleteBeforeDate --
IF @DeleteBeforeDate is null
select @DeleteBeforeDate = (Select top 1 FDate 
from #dirlist 
where right(FName,3) = @Extension 
order by FDate DESC)
--select @DeleteBeforeDate as DeleteBeforeDate

--delete record for most recent backup
delete from #dirlist where FDate >= @DeleteBeforeDate



--  Create a cursor and for each file name do the processing.
--  The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR 
    SELECT  FName
        FROM #dirlist
            WHERE  FDate <  @DeleteBeforeDate

OPEN curDir

FETCH NEXT FROM curDir INTO @FName
WHILE (@@fetch_status = 0)
BEGIN

    -- Delete the old backup files
    SET @Delete = 'DEL "'+ @Path + @FName + '"'

    INSERT INTO #Errors (Results) 
        exec master..xp_cmdshell @Delete
--    select @Delete as asdfdelete          

    IF @@RowCount > 1
    BEGIN
        SET @Error = -1
        SET @Msg = 'Error while Deleting file ' + @FName
        GOTO On_Error
    END


--    PRINT @Delete
    PRINT 'Deleted ' + @FName + '  at ' +  CONVERT(VARCHAR(28),GETDATE(),113)

FETCH NEXT FROM curDir INTO @FName
END

CLOSE curDir
DEALLOCATE curDir
DROP TABLE #dirlist
DROP TABLE #Errors



On_Error:
BEGIN
    IF @Error <> 0
    BEGIN
        SELECT @Msg + '.  Error # ' + CAST(@Error AS VARCHAR(10))
        RAISERROR(@Msg,12,1)
        RETURN @Error
    END
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO