CREATE Proc spCycleFiles
/****************************************************************************************
Description:      Procedure to round-robin files.
            This is used in the backup process to prevent too many files
accumulating in the backup directory.
            The oldest file is removed first, and so on until the number of
files = @NumberStored
            The flag @IsLog is used to distinguish between file extensions
of database backups vs log backups, as only files of these
            extensions are deleted
Returns:    (None)
Author:           Paul Ibison (xt 26163)
Date Created:     22/12/2004
Revisions:
*****************************************************************************************/
      @BackupPath varchar(300) = '' ,
      @NumberStored tinyint = 3 ,
      @IsLog bit = 1
as

SET NOCOUNT ON

Declare @FileName Varchar(1000)
Declare @Delete Varchar(1000)
Declare @Msg Varchar(1000)
Declare @DirCmd Varchar(1000)
Declare @fileextension varchar(100)
Declare @count int
Declare @startFilenamepos int
Declare @searchclause varchar(1000)

set @count = 1

--SET @BackupPath = 'C:\misc\test\'

if @IsLog = 1
      set @fileextension = 'trn'
else
      set @fileextension = 'bak'

set @searchclause = '%.' + @fileextension

PRINT 'File Extension: ' + @fileextension
PRINT 'Search clause: ' + @searchclause

IF OBJECT_ID('tempdb..#tmprevdirlist') IS NOT NULL DROP TABLE #DirList
IF OBJECT_ID('tempdb..#revdirlist') IS NOT NULL DROP TABLE #DirList
IF OBJECT_ID('tempdb..#DeleteErrors') IS NOT NULL DROP TABLE #DeleteErrors

CREATE TABLE #tmprevdirlist (FileName VARCHAR(1000))
CREATE TABLE #revdirlist (FileName VARCHAR(1000),FileNo smallint  null)
CREATE TABLE #DeleteErrors (Results VARCHAR(1000))

--  Insert the results of the dir cmd in reverse into a table so we can
scan it
set @DirCmd = 'dir /O-D ' + @BackupPath + '*.' + @fileextension
PRINT 'Dir command: ' + @DirCmd

INSERT INTO #tmprevdirlist (FileName) exec master..xp_cmdshell @DirCmd
IF @@ERROR <> 0
BEGIN
    RAISERROR('Error while getting the filenames with DIR',12,1)
    Return
END

-- need a separate table to hold the filenumber
insert into #revdirlist(filename)
select filename from #tmprevdirlist

--  Remove the garbage
DELETE #revdirlist WHERE SUBSTRING(FileName,1,2) < '00'
                  OR SUBSTRING(FileName,1,2) > '99'
                  OR FileName IS NULL
                  OR FileName not LIKE @searchclause

-- Populate the fileno field
DECLARE curCount CURSOR LOCAL
FOR
    SELECT SUBSTRING(FileName,37,100) AS FName
    FROM #revdirlist

OPEN curCount
FETCH NEXT FROM curCount INTO @FileName
WHILE (@@fetch_status = 0)
BEGIN
    update #revdirlist set fileno = @count where current of curCount
    set @count = @count + 1
    FETCH NEXT FROM curCount INTO @FileName
END
CLOSE curcount
DEALLOCATE curcount

--select * from #revdirlist

--  Create a cursor and for each file name do the processing.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
    SELECT FileName AS FName
    FROM #revdirlist
    WHERE FileNo > @NumberStored

OPEN curDir

FETCH NEXT FROM curDir INTO @FileName
WHILE (@@fetch_status = 0)
BEGIN
      print 'Filename: ' + @FileName
      set @startFilenamepos = dbo.udf_Space_CharIndexRev(@FileName) + 1
      print 'Space Position: ' + cast(@startFilenamepos as varchar(10))
      set @FileName = SUBSTRING(@FileName,@startFilenamepos,100)
      print 'Modified filename: ' + @FileName
      -- Delete the old backup files
      SET @Delete = 'DEL ' + @BackupPath + @FileName
      PRINT 'Delete statement: ' + @Delete
      INSERT INTO #DeleteErrors (Results)
      exec master..xp_cmdshell @Delete

      IF @@RowCount > 1
      BEGIN
                  SET @Msg = 'Error while Deleting file ' + @FileName
                  RAISERROR(@Msg,12,1)
                  Return
      END
      PRINT 'Deleted ' + @FileName + '  at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
    FETCH NEXT FROM curDir INTO @FileName
END

CLOSE curDir
DEALLOCATE curDir


DROP TABLE #revdirlist
DROP TABLE #DeleteErrors
DROP TABLE #tmprevdirlist
GO