| Author |
 Topic  |
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
 Posted - 08/22/2006 : 12:48:10
|
I know this is NOT the correct forum for this query. But any help is appreciated.
I need to delete older than 7 days files from a folder on a regular basis. (I can not use xp_cmdshell)
Does anyone have .bat code?
------------------------
I think, therefore I am - Rene Descartes
|
|
|
Wanderer
Flowing Fount of Yak Knowledge
United Kingdom
1167 Posts |
Posted - 08/22/2006 : 13:06:23
|
Nothing it .bat's, but a thought (if you can use .cmd) that maybe you could play around with %date% ?
HTH
*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 08/22/2006 : 14:19:26
|
If you cannot use XP_CMDSHELL, how would you execute a .bat file?
You could use the file system objects with OA extended stored procedures or in an ActiveX script in a DTS package.
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 08/22/2006 14:19:56 |
|
|
|
Wanderer
Flowing Fount of Yak Knowledge
United Kingdom
1167 Posts |
Posted - 08/23/2006 : 05:17:24
|
I got the feeling this isn't SQL at all. I think he's going to run the .bat (or .cmd) from a sql server agent job as a 'operating system command' step type...
*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here! |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
|
|
Sitka
Aged Yak Warrior
USA
571 Posts |
Posted - 09/07/2006 : 10:23:57
|
here's a part of something I use.
it's a .vbs script that writes a log file to audit last modified dates of files.
I then BCP the logfile into an evaluation table, like this
CREATE TABLE fileaudit (
fullpath varchar (255) NULL ,
hoursold int NULL ,
[filename] varchar (255) NULL
)
GO
Once in an SQL table you can evaluate the path to define "if any" file within a folder has been recently modified. Then I email that folder list to the people who are responsible for archiving those folders.
It would be a simple step to set the requirements to delete a file or folder rather than include it in a mailer.
Dim Logfile, objFSO, objWSH, objLogFile
Set objWSH = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
IF ISOBJECT(objFSO) then
'WScript.Echo ("hi")
END if
strRootFolder = LCase(WScript.Arguments(0))
'WScript.Echo (strRootFolder)
intMaxHoursOld = LCase(WScript.Arguments(1))
'WScript.Echo (intMaxHoursOld)
If Not objFSO.FolderExists(strRootFolder ) Then
' Call Fail("Can't find " & chr(34) & RootFolder & chr(34) & ".")
'WScript.Echo ("not a folder")
Else
'WScript.Echo ("nice folder : " & strRootFolder)
End If
Logfile = "F:\fileaudit\fileaudit.log"
Set objLogFile = objFSO.CreateTextFile(Logfile, True)
Call ScanFolder(strRootFolder)
Sub ScanFolder(sFolderName)
Dim objFolder, objSubFolders, objFiles
Dim Folder, File
Dim ret
On Error Resume Next
Set objFolder = objFSO.GetFolder(sFolderName)
Set objSubFolders = objFolder.SubFolders
Set objFiles = objFolder.Files
For Each Folder In objSubFolders
ScanFolder (Folder.Path)
Next
For Each File In objFiles
ret = HoursOld(File.Path)
' If CINT(ret) < CINT(intMaxHoursOld) Then
'WScript.Echo("true")
WScript.Echo(REPLACE(TRIM(File.Path),"|","_") & "|" & ret & "|" & REPLACE(TRIM(objFSO.GetFile(File.Path).Name),"|","_"))
Call LogEvent(REPLACE(TRIM(File.Path),"|","_") & "|" & ret & "|" & REPLACE(TRIM(objFSO.GetFile(File.Path).Name),"|","_"))
'Call LogEvent(TRIM(File.Path) & "|" & ret & "|" & TRIM(objFSO.GetFile(File.Path).Name))
' Call CheckError
' End If
Next
End Sub
Function HoursOld(sFileName)
Dim objFile
On Error Resume Next
Set objFile = objFSO.GetFile(sFileName)
Call CheckError
' Return the difference in hours.
HoursOld = Round(Cdbl(Now() - objFile.DateLastModified)*24)
End Function
Sub LogEvent(sMessage)
WScript.Echo(sMessage)
objLogFile.WriteLine(sMessage)
End Sub
Sub CheckError()
Dim msg
If Err.Number = 0 Then Exit Sub
msg = Err.Source & " " & Hex(Err) & ": " & Err.Description
Call Fail(msg)
End Sub
Here's an sample of the audit log
between the two |xxxxx| (bcp delimiter) you get a return value for the hours old.
O:\2657\Design\2657T1\active\diameters.cdx|20535|diameters.cdx
O:\2657\Design\2657T1\active\diameters.dbf|20535|diameters.dbf
O:\2657\Design\2657T1\igs\10910-1.igs|20535|10910-1.igs
O:\2657\Design\2657T1\plot\2657CC.PLT|20459|2657CC.PLT
O:\2657\Design\2657T1\2657CC.PLT|20459|2657CC.PLT
O:\2657\Design\2657T1\2657t1.bak|20457|2657t1.bak
O:\2657\Design\2657T1\2657t1.jnl|20442|2657t1.jnl
O:\2657\Design\2657T1\2657T1.pin|20535|2657T1.pin
O:\2657\Design\2657T1\2657t1.prt|20457|2657t1.prt
O:\2657\Design\2657T1\2657t1.tbi|20457|2657t1.tbi
O:\2657\Design\2657T1\2657t1.xmd|20457|2657t1.xmd
O:\2657\Design\2657T1\2657t1.xmd.bak|20457|2657t1.xmd.bak
O:\2657\Design\2657T1\active.cfg|20457|active.cfg
O:\2657\Design\2657T1\active.wl|20457|active.wl
O:\2657\Design\2657T2\active\diameters.cdx|20442|diameters.cdx
O:\2657\Design\2657T2\active\diameters.dbf|20442|diameters.dbf
O:\2657\Design\2657T2\igs\10910-1.igs|20442|10910-1.igs
O:\2657\Design\2657T2\2657t2.bak|19457|2657t2.bak
O:\2657\Design\2657T2\2657t2.jnl|19733|2657t2.jnl
O:\2657\Design\2657T2\2657t2.out|19735|2657t2.out
O:\2657\Design\2657T2\2657T2.pin|19783|2657T2.pin
O:\2657\Design\2657T2\2657t2.prt|19456|2657t2.prt
O:\2657\Design\2657T2\2657t2.tbi|19456|2657t2.tbi
O:\2657\Design\2657T2\2657t2.xmd|19456|2657t2.xmd
O:\2657\Design\2657T2\2657t2.xmd.bak|19457|2657t2.xmd.bak
O:\2657\Design\2657T2\active.cfg|19456|active.cfg
O:\2657\Design\2657T2\active.wl|19456|active.wl
O:\2657\Design\2657T3\active\diameters.cdx|19193|diameters.cdx
O:\2657\Design\2657T3\active\diameters.dbf|19193|diameters.dbf
O:\2657\Design\2657T3\igs\10910-1.igs|19193|10910-1.igs
O:\2657\Design\2657T3\2657t3.bak|18401|2657t3.bak
O:\2657\Design\2657T3\2657t3.jnl|16767|2657t3.jnl
O:\2657\Design\2657T3\2657t3.out|19037|2657t3.out
O:\2657\Design\2657T3\2657T3.pin|19034|2657T3.pin
O:\2657\Design\2657T3\2657t3.prt|18401|2657t3.prt
O:\2657\Design\2657T3\2657t3.tbi|18401|2657t3.tbi
O:\2657\Design\2657T3\2657t3.xmd|18401|2657t3.xmd
O:\2657\Design\2657T3\2657t3.xmd.bak|19039|2657t3.xmd.bak
O:\2657\Design\2657T3\active.cfg|18401|active.cfg
O:\2657\Design\2657T3\active.wl|18401|active.wl
O:\2657\Design\2657T4\active\diameters.cdx|15827|diameters.cdx
O:\2657\Design\2657T4\active\diameters.dbf|15827|diameters.dbf
O:\2657\Design\2657T4\igs\10910-1.igs|15827|10910-1.igs
O:\2657\Design\2657T4\2657t4.bak|6314|2657t4.bak
O:\2657\Design\2657T4\2657t4.jnl|6314|2657t4.jnl
O:\2657\Design\2657T4\2657T4.pin|14003|2657T4.pin
O:\2657\Design\2657T4\2657t4.prt|6314|2657t4.prt
O:\2657\Design\2657T4\2657t4.tbi|6314|2657t4.tbi
O:\2657\Design\2657T4\2657t4.xmd|6314|2657t4.xmd
O:\2657\Design\2657T4\2657t4.xmd.bak|6314|2657t4.xmd.bak
O:\2657\Design\2657T4\active.cfg|6314|active.cfg
O:\2657\Design\2657T4\active.wl|6314|active.wl
with the hours old value and the path you can script the conditional deletion, at the folder or file level. Since my audit is based on all or nothing moving of folder to archive it I have some evaluation and grouping by portions of the fullpath.
I generate the audit log once a week off hours takes about 1.5 hours
to audit the ~1/4 million files. Rest of the stuff is instant.
Just to complete the story here is the result of the audit
"2856",150
"2857",156
"2861",157
"2862",133
"2868t3",107
"2878",117
"2882",154
"2883",129
"2885",124
....
this is read as of all the files in folder O:\2856 (there could be lots) the most recently modified one is 150 days old.
"it's definitely useless and maybe harmful". |
Edited by - Sitka on 09/07/2006 10:42:36 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/07/2006 : 11:50:28
|
For this type of thing we use a batch file and:
Move all the relevant files into a subfolder called, say, "ARCHIVE01"
Then tomorrow we rename ARCHIVE01 to ARCHIVE02
And after 7 days, when it has been renamed to ARCHIVE07, we delete that folder ... so
RMDIR /Q ARCHIVE07
MOVE ARCHIVE06 ARCHIVE07
...
MOVE ARCHIVE01 ARCHIVE02
MKDIR ARCHIVE01
MOVE *.BAK ARCHIVE01
Kristen |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 09/07/2006 : 12:14:34
|
quote:
RMDIR /Q ARCHIVE07
MOVE ARCHIVE06 ARCHIVE07
...
MOVE ARCHIVE01 ARCHIVE02
MKDIR ARCHIVE01
MOVE *.BAK ARCHIVE01
Kristen
This is nice. Thank you.
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/07/2006 : 12:46:18
|
Note that it only keeps the files for the last 7 times the routine is RUN, not the last 7 days necessarily!!!
But at least it will keep the situation "under control" ... which may be all you need.
Kristen |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 09/08/2006 : 02:33:51
|
quote:
Originally posted by Kristen
Note that it only keeps the files for the last 7 times the routine is RUN, not the last 7 days necessarily!!!
As long as you are running once a day, the purpose will be achieved.
But will it not be little bit difficult, when the retention period is long? Lets suppose you want to retain, last 6 months data.
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/08/2006 : 03:30:09
|
I have BATch files here that "cycle" 150 folders. Lots of lines in the batch file, but I used a macro to create them  ... Of course its hard to "guess" which folder is the 3rd August ... but if you only need to do that once in a while its OK.
Clearly a proper "delete earlier than this date" is a better approach, and this is only intended to be a cheap & cheerful way without installing 3rd party apps etc.
Kristen |
|
|
| |
Topic |
|
File deletion script.
Forum Locked
