SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 File deletion script.
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 08/22/2006 :  12:48:10  Show Profile
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  Show Profile
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!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/22/2006 :  14:19:26  Show Profile
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
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 08/23/2006 :  05:17:24  Show Profile
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!
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 09/07/2006 :  05:57:55  Show Profile
Got the script, Check the article,

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


------------------------
I think, therefore I am - Rene Descartes
Go to Top of Page

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 09/07/2006 :  10:23:57  Show Profile
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/07/2006 :  11:50:28  Show Profile
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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 09/07/2006 :  12:14:34  Show Profile
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/07/2006 :  12:46:18  Show Profile
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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 09/08/2006 :  02:33:51  Show Profile
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/08/2006 :  03:30:09  Show Profile
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000