Deleting OS files from a stored procedure: 

 Here is one way to remove old backup files from the OS through a stored procedure.  The procedure can be compiled in any database.  Once compiled you can run the stored procedure from a scheduled job to remove old backup files from the OS.

IF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name] = 'usp_Delete_OS_Files_By_Date' AND TYPE = 'P') 

DROP PROCEDURE dbo.usp_Delete_OS_Files_By_Date

GO

CREATE PROCEDURE dbo.usp_Delete_OS_Files_By_Date (@SourceDir varchar(255), @SourceFile varchar(255), @DaysToKeep int)

AS

BEGIN

/***********************************************************************************    

Description:  Delete operating system files older than n days                   ****      

Prototype:

EXEC dbo.usp_Delete_OS_Files_By_Date                                     

                                      @SourceDir = '\smpprod01BackupsSGC'

                                    , @SourceFile = 'SGC_Backup_*'

                                    , @DaysToKeep = 3                 

                                                                                ****

History                                                                         ****

05/06/2011 Kim Tessereau      Created                                           ****

************************************************************************************/

SET NOCOUNT ON;

DECLARE @CurrentFileDate char(10),

@OldFileDate char(10),

@SourceDirFOR varchar(255),

@FileName varchar(255),

@DelCommand varchar(255);

SET @CurrentFileDate = CONVERT(char(10),getdate(),121);

SET @OldFileDate = CONVERT(char(10),DATEADD(dd,-@DaysToKeep,@CurrentFileDate),121);

SET @SourceDirFOR = 'FOR %I IN ("' + @SourceDir + @SourceFile + '") DO @ECHO %~nxtI';

— Get OS File information from the OS

CREATE TABLE #Temp_01      ( [lineorder] int IDENTITY(1,1)  

                           , [OSInfo] varchar(255) );

INSERT INTO #Temp_01

EXEC master..xp_cmdshell @SourceDirFOR;

— Put the OS File info in date order

CREATE TABLE #Temp_02      ( [lineorder] int   

                           , [TimeStamp] datetime

                           , [FileName] varchar(255) );

INSERT INTO #Temp_02 ([lineorder], [Timestamp], [FileName])

SELECT [lineorder]

      , CONVERT (char(10),SUBSTRING([OSInfo],1,10), 121) AS [TimeStamp]                     

      , SUBSTRING([OSInfo],21,255) AS [FileName]                      

FROM #Temp_01                  

WHERE [OSInfo] IS NOT NULL               

ORDER BY [lineorder];

— Loop through OS Information and create delete command and execute

DECLARE DeleteCursor CURSOR READ_ONLY FOR 

SELECT [FileName]     

FROM #Temp_02

WHERE [TimeStamp] <= @OldFileDate;

OPEN DeleteCursor;

FETCH NEXT FROM DeleteCursor INTO @FileName;

WHILE (@@fetch_status <> –1)

BEGIN

      IF (@@fetch_status <> –2)   

            BEGIN     

            SET @DelCommand = 'DEL /Q "' + @SourceDir + @FileName + '"';    

            EXEC master..xp_cmdshell @DelCommand;

                  END 

FETCH NEXT FROM DeleteCursor INTO @FileName;

END;

CLOSE DeleteCursor;

DEALLOCATE DeleteCursor;

END;

GO