SQL Script for deleting backup files

Question

Post reply

SQL Script for deleting backup files

coenzyme77

SSCommitted

Points: 1669
More actions
July 8, 2008 at 2:01 pm

#72479

Hi everyone. I'm attempting to write or locate a script that will delete backup files that are less than X number of hours old. I located this VB script, but it fails to run as a job. Can anyone tell me if there is a problem with it, and or is there a VB function I could use to call hours instead of days. Or if anyone knows another script that will serve the same purpose, it would be greatly appreciated.
R.,
Ed SQL 2000 Standard SP4
Here's the script:
iDaysOld = 3
strPath = "E:\DeleteTestFolder"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
Set colSubfolders = objFolder.Subfolders
Set colFiles = objFolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() - iDaysOld) Then
MsgBox "Dir: " & objFolder.Name & vbCrLf & "File: " & objFile.Name
'objFile.Delete
End If
Next
For Each objSubfolder in colSubfolders
Set colFiles = objSubfolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() - iDaysOld) Then
MsgBox "Dir: " & objSubfolder.Name & vbCrLf & "File: " & objFile.Name
'objFile.Delete
End If
Next
Next

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Have you gone thru the script mentioned on

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_23419233.html

Manu

sebastien piche Ten Centuries Points: 1070 More actions · Answer 2

i'm using this script

Const FILE_NAME = 0

folder = "\\zz60\d$\SQLBackupTemp\"

'get files name into folder

Set objShell = CreateObject ("Shell.Application")

Set objFolder = objShell.Namespace (folder)

For Each strFileName in objFolder.Items

'pour chaque fihier j'appel datecr

f = objFolder.GetDetailsOf (strFileName, FILE_NAME)

Call datecr

Sub datecr

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.GetFile(folder&f)

theDate = objFile.DateCreated

a = DiffADate(theDate)

'if older 15 day

If a <= -15 Then objFSO.DeleteFile(folder&f) End if

End Sub

'function compare date

Function DiffADate(theDate)

DiffADate = DateDiff("d", Now, theDate)

End Function

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Suggest you look at this function available in VB:

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

The intervalargument has these settings:

Setting Description

yyyy Year

q Quarter

m Month

y Day of year

d Day

w Weekday

ww Week

h Hour

n Minute

s Second

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

coenzyme77 SSCommitted Points: 1669 More actions · Answer 4

Thanks for the reply Manu. It's great to be able to share ideas.

coenzyme77 SSCommitted Points: 1669 More actions · Answer 5

Thanks for taking the time to respond Sebastien. I'm looking forward to testing this.

coenzyme77 SSCommitted Points: 1669 More actions · Answer 6

coenzyme77

SSCommitted

Points: 1669

July 11, 2008 at 8:39 am

#841553

Thanks Bitbucket. Looks promising.