Post reply

SQL Script for deleting backup files

  • 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

  • July 9, 2008 at 3:26 pm

    #840538

    Have you gone thru the script mentioned on

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

    Manu

  • July 10, 2008 at 8:28 am

    #840865

    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

    Next

    'get date files

    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

  • July 10, 2008 at 10:03 am

    #840944

    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]

  • July 11, 2008 at 8:34 am

    #841548

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

  • July 11, 2008 at 8:38 am

    #841552

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

  • July 11, 2008 at 8:39 am

    #841553

    Thanks Bitbucket. Looks promising.

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

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