April 6, 2005 at 11:16 am
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
April 6, 2005 at 11:55 am
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
April 6, 2005 at 12:55 pm
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
April 6, 2005 at 2:18 pm
Funny Steve, we must have posed at the same time in different timezones
KlK
April 6, 2005 at 2:52 pm
Nah, you were there when I posted. I don't know where my brain was, but it obviously wasn't in my head.
Steve
April 6, 2005 at 2:52 pm
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
Next
Set fs=Nothing
April 6, 2005 at 3:01 pm
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
April 6, 2005 at 5:29 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply