vbscript to delete old backup files
-
I'm using the below vbscript from a sqlagent job to delete old backup files older than 1 day.
It's working file in SQL Server 2005.
Now we upgraded the instance to SQL Server 2008.
After the upgrade the vbscript is NOT deleting the old backup files any more.
sqlagent runs successfully but NOT deleting any backup files.
Is there any methods available to delete old backup file other than Maintenance plan?
Please advise.
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:\PROD\Backups\Full\"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("D:\MSSQL10.PROD\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.DateCreated < (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
-
gmamata7 (3/28/2012)
I'm using the below vbscript from a sqlagent job to delete old backup files older than 1 day.It's working file in SQL Server 2005.
Now we upgraded the instance to SQL Server 2008.
After the upgrade the vbscript is NOT deleting the old backup files any more.
sqlagent runs successfully but NOT deleting any backup files.
Is there any methods available to delete old backup file other than Maintenance plan?
Please advise.
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:\PROD\Backups\Full\"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("D:\MSSQL10.PROD\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.DateCreated < (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
You may find this link useful. Success! 😉
-
First and foremost you shot your own foot off with this.
On Error Resume Next
As this will just skip the error and report success, so please remove because you want to catch the error. Once you know the error you will know what to do.
Also, personally I would consider creating a maintenance plan to manage this instead but I see you don't want that.
-
gmamata7 (3/28/2012)
Is there any methods available to delete old backup file other than Maintenance plan?I have to ask "Why"? The Maintenance plan deletes work just fine. Why don't you want to use it?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply