Deleting Old backup files in SQL Server 2008 is NOT working

Question

Deleting Old backup files in SQL Server 2008 is NOT working

gary1

SSC-Insane

Points: 21152
More actions
September 14, 2010 at 11:11 am

#227778

Hi,
In SQL Server 2005 x64, I'm using the below VB script to delete the old backup files via SQL Server Agent job.
Now I have upgraded to SQL Server 2008 x64 from SQL Server 2005 using side-by-side upgrade method. During this , I have scripted all the jobs fron SQL 2005 and ran into SQL 2008 and all jobs are running fine But the job deleting the Old backup files is NOT wroking.
It gives NO error. The runs successfully but NOT deleting the old bak files. Please advice. Thanks
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim Message
Dim iDaysOld
Dim oFSFile
Dim objTextFile
Dim objFSO
Const ForAppending = 8
'Customize values here to fit your needs
iDaysOld = 2
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "Z:\Backups\Full\"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("D:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.txt", ForAppending, True)
'Walk through each file in this folder collection.
'If it is older than 2 days, then delete it.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
objTextFile.WriteLine Date & " " & Time & " Deleting File " & oFile.name
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
objTextFile=Nothing

Viewing 2 posts - 1 through 1 (of 1 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i've been lucky, not having to do vb6 for a while as we switched over to .NET, so this is untested.

i think your issue is an error is raised (file locked for example) and you are using On Error Resule Next, instead of testing and looking for the error.

change the middle of your code to test for the error, something like this:

'Walk through each file in this folder collection.

'If it is older than 2 days, then delete it.

For Each oFile In oFileCollection

If oFile.DateLastModified < (Date - iDaysOld) Then

objTextFile.WriteLine Date & " " & Time & " Deleting File " & oFile.Name

oFile.Delete (True)

If Err.Number > 0 Then

objTextFile.WriteLine Date & " " & Time & "Error " & Err.Number & " (" & Err.Description & ") in VBScript Deleting Old backup files"

Else

End If

Next

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!