deleting files older than a certain date/time

Question

deleting files older than a certain date/time

Gabriela Nanau

SSCommitted

Points: 1967
More actions
April 6, 2005 at 11:16 am

#166537

Hi,
Does anybody have a script that might be run as a SQL Server job and that would delete files older than a certain date/time from a certain folder?
Any help highly appreciated.

Gabriela

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 1

Not exactly what you need, but should help. This deletes greater than n Backups. It also has the Date as part of the file name. But see what is returned from the DIR and you may get the date.

CREATE PROCEDURE XXXXXXXDeleteBkUps

@BkUps2Keep Integer = 7

AS

DECLARE @Cmd VarChar(255)

DECLARE @FileName VarChar(255)

DECLARE @Cnt Integer

CREATE TABLE #BackupFileList

(

BackupFileName varchar(255)

)

INSERT #BackupFileList Execute master..xp_cmdshell 'dir J:\XXXX\*.CPY /B'

DELETE #BackupFileList WHERE BackupFileName IS NULL

SELECT @Cnt = Count(*) FROM #BackupFileList

WHILE @Cnt > @BkUps2Keep

BEGIN

SELECT TOP 1 @FileName = BackupFileName From #BackupFileList Order by BackupFileName

SET @Cmd = 'DEL J:\XXXXX\' + @FileName

Execute master..xp_cmdshell @Cmd

DELETE FROM #BackupFileList WHERE BackupFileName = '' + @FileName + ''

SELECT @Cnt = Count(*) FROM #BackupFileList

END

Drop Table #BackupFileList

GO

KlK

hoo-t SSChampion Points: 12713 More actions · Answer 2

I have a stored procedure that deletes files based on a date/time stamp in the filename. It wouldn't be too much of a stretch to go from there to use the OS date/time stamp. My proc pulls directory information (just the filename) into a temporary table, then processes it from there. But, you could pull the date/time from the DIR command as well. If you would like to have the procedure, send me a private message, and I'll be glad to provide it to you.

Steve

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 3

Funny Steve, we must have posed at the same time in different timezones

KlK

hoo-t SSChampion Points: 12713 More actions · Answer 4

Nah, you were there when I posted. I don't know where my brain was, but it obviously wasn't in my head.

Steve

Gabriela Nanau SSCommitted Points: 1967 More actions · Answer 5

I think I figured out a way that will remove all files older than 7 days. I will test it in the next days, but it should do the trick. Thanks you all for answers, but I do not want to parse the file name, I want someting that will really use the last modified when the file was changed.

I set this up as a job with a sigle ActiveX/Visual Basic step:

Dim fs, f, fd, s

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.getfolder("C:\TEST")

Set fd = f.Files

For Each f1 in fd

If f1.DateLastModified<DateAdd("d",-7,Date) Then f1.Delete

hoo-t SSChampion Points: 12713 More actions · Answer 6

KlK,

Now that my brain is back where it belongs, I was looking at your script. Its a very interesting approach, and much, much simpler than mine. I actually parse the date out of the filename, get the age in days and compare that to a retention value to determine which to keep and which to delete. I think I'll hang on to your approach for future reference!

Gabriela,

I know next to nothing about ActiveX/VB, but from looking at your script, it, too, is much more elegant than mine.

Steve

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 7

I just thought it was interesting that our times were exactly the same, 1 hour apart.

Thanks much for the compliments, from you I take that with the highest regards.

Although it did cause some problems as I originally had the date in the wrong format and was deleting the wrong file. Oops :-}

It's actually paired with another SP that backs up a Navision (pre SQL version) DB that lives on the same server, so all I was trying to do was emulate SQLs keep n copies on-line.

Thanks again.

KlK, MCSE

KlK