File deletion script. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

File deletion script.

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-08-22 : 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
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-22 : 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)

7020 Posts

Posted - 2006-08-22 : 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

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-23 : 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
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-09-07 : 05:57:55

Got the script, Check the article,

http://www.sqlservercentral.com/columnists/cmiller/2586.asp

------------------------
I think, therefore I am - Rene Descartes

Sitka
Aged Yak Warrior

571 Posts

Posted - 2006-09-07 : 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".

Kristen
Test

22859 Posts

Posted - 2006-09-07 : 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
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-09-07 : 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

22859 Posts

Posted - 2006-09-07 : 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
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-09-08 : 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

22859 Posts

Posted - 2006-09-08 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources