Home Forums SQL Server 2005 T-SQL (SS2K5) Delete files olde than 48 hours based on filename, not date modified. RE: Delete files olde than 48 hours based on filename, not date modified.

  • January 30, 2008 at 10:17 am

    #773407

    Hi guys,

    I came up with this code. and it works:)

    declare @d as varchar (8)

    declare @d1 as varchar (12)

    DECLARE @NewFileName VarChar(30)

    DECLARE @SQL_Command VarChar(200)

    DECLARE @SQL_Command1 VarChar(200)

    DECLARE @SQL_Command2 VarChar(200)

    DECLARE @SQL_Command3 VarChar(200)

    DECLARE @BACKUP_DATE as VARCHAR(64)

    DECLARE @BACKUP_DATE1 as VARCHAR(64)

    DECLARE @BACKUP_DATE2 as VARCHAR(64)

    SET @BACKUP_DATE = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())

    as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate())

    as varchar(4))))+cast(day(getdate()) as varchar(4))+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)

    -- + SUBSTRING(CONVERT(Char,GetDate(),120),15,2)

    --print @BACKUP_DATE

    SET @BACKUP_DATE1 = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())

    as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate()-3)

    as varchar(4))))+cast(day(getdate()-3) as varchar(4))

    --+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)

    --+ SUBSTRING(CONVERT(Char,GetDate(),120),15,2)

    SET @BACKUP_DATE2 = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())

    as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate()-2)

    as varchar(4))))+cast(day(getdate()-2) as varchar(4))+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)

    --+ SUBSTRING(CONVERT(Char,GetDate(),120),15,2)

    --print @BACKUP_DATE1

    --xp_cmdshell 'FORFILES /p w:\Backups\ESSBASE /m *.* /s /c "CMD /C del @FILE" /d -2'

    SET @SQL_Command = 'FORFILES /p w:\Backups\ESSBASE /m ESSBASE_' + @BACKUP_DATE1 + '*.bak /s /c "CMD /C del @FILE"'

    SET @SQL_Command1 = 'xp_cmdshell ''' +@SQL_Command+''''

    print @SQL_Command1

    EXEC (@SQL_Command1)

    SET @SQL_Command2 = 'FORFILES /p w:\Backups\ESSBASE /m ESSBASE_' + @BACKUP_DATE2 + '*.bak /s /c "CMD /C del @FILE"'

    --print @SQL_Command

    SET @SQL_Command3 = 'xp_cmdshell ''' +@SQL_Command2+''''

    print @SQL_Command3

    EXEC (@SQL_Command3)

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

    I have anothe issue now. I have tried to do the same with xp_delete_file and i can not make it work. I will probably add a separate post for this issue:)