VB script to delete old backups is NOT working in SQL Server 2008 R2

Question

VB script to delete old backups is NOT working in SQL Server 2008 R2

Mani-584606

SSChampion

Points: 11788
More actions
December 12, 2011 at 11:56 am

#253287

Hi,
I'm using the below VB script to delete the old backup files via SQLAgent and it's working fine in SQL Server 2005.
But it's NOT working in SQL Server 2008 R2. The SQLAgent job finishes successfully without deleting the old backup files.
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 = 1
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\Delete_FullBakFiles.txt", ForAppending, True)
'Walk through each file in this folder collection.
'If it is older than 1 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
Please advice

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

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

Please verify the path\file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\Delete_FullBakFiles.txt'. It is not valid for SQL Server 2008.

For More:

http://msdn.microsoft.com/en-us/library/ms143547.aspx

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Verify the path, if it's not working, include some MSGBOX lines in there, and see what values are being sent for the files and folders. It's basic debugging here for your system.