May 24, 2007 at 9:58 am
I would like to delete all old trans log on regular basis. Right now I am putting each of them in diff directories. I used following solution earlier:
http://qa.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp
which is useful to delete files from a directory and not sub folders. I would like it to delete everything older than a specific time period including parent folder and sub folders.
any thoughts how to accomplish that?
thanks
May 24, 2007 at 10:05 pm
Yep... do a backup.
--Jeff Moden
May 25, 2007 at 3:40 am
If you are using vbscript to generate your file deletes, then Google for information about the FilesystemObject. This has properties that can give you the creation date of each file, and from this you can decide if you want to delete it.
If you are using T-SQL to generate file deletes, then you can use INSERT INTO #table EXEC xp_cmdshell "DIR ..." to get details of the files in your backup folder into a table. You can then look at each file with a cursor and decide if the creation date meets your criteria for deletion.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 25, 2007 at 8:57 am
May 25, 2007 at 10:06 am
Here's one I've used for a while. Don't remember where I got it.
' Objective: To delete old files from a given folder and all subfolders below
' Created by: MAK
' Created Date: June 21, 2005
' Usage: cscript deloldfiles.vbs c:\dba\log 3 BAK
' : It deletes files older than 3 days with a BAK extension
Set objArgs = WScript.Arguments
FolderName =objArgs(0)
Days=objArgs(1)
Extension=ObjArgs(2)
set fso = createobject("scripting.filesystemobject")
set folders = fso.getfolder(FolderName)
datetoday = now()
newdate = dateadd("d", Days*-1, datetoday)
wscript.echo "Today:" & now()
wscript.echo "Started deleting files older than :" & newdate
wscript.echo "________________________________________________"
wscript.echo ""
recurse folders
wscript.echo ""
wscript.echo "Completed deleting files older than :" & newdate
wscript.echo "________________________________________________"
sub recurse( byref folders)
set subfolders = folders.subfolders
set files = folders.files
wscript.echo ""
wscript.echo "Deleting Files under the Folder:" & folders.path
wscript.echo "__________________________________________________________________________"
for each file in files
if file.datelastmodified < newdate AND right(file.name, len(Extension)+1) = "." & Extension then
wscript.echo "Deleting " & folders.path & "\" & file.name & " last modified: " & file.datelastmodified
on error resume next
file.delete
end if
next
for each folder in subfolders
recurse folder
next
set subfolders = nothing
set files = nothing
end sub
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply