February 1, 2009 at 11:19 pm
Hi,
We are using this VB script as a sql agent job to delete backup files older than a week.But we would like to know which files it is deleting.What will be the logic to add logging options(what it is deleting) for this script?
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Customize values here to fit your needs
iDaysOld = 21
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "FolderName here. Can be UNC path like \\MyServer\MyFolder"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'Walk through each file in this folder collection.
'If it is older than 3 weeks (21) days, then delete it.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
February 8, 2009 at 1:38 pm
could you please provide me some kind of script which deletes old backup files. Currently Iam using xp_cmdshell, which is not recommanded as I read many times in this forum.So please give some vbscript or windows script to delete old backups and I want to schedule it as a sqlagent job or windows schedular job.
Thank You
February 9, 2009 at 2:02 am
u can use this function as
LogMessageToFile(Filename)
Public Sub LogMessageToFile(ByVal Message As String)
Dim LogFileName As String = "C:\LogDeletedFile.txt"
Dim strTextToWrite As String = Message
Dim objFileStream As FileStream
If Not (File.Exists(LogFileName)) Then
objFileStream = File.Create(LogFileName)
objFileStream.Close()
End If
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 9, 2009 at 11:17 am
Thank You
Where exactly I can insert this script in the original script I posted?
February 9, 2009 at 10:08 pm
kotlas7 (2/9/2009)
Thank YouWhere exactly I can insert this script in the original script I posted?
Here is da complete script...
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim Message
Dim iDaysOld
'Customize values here to fit your needs
iDaysOld = 21
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "FolderName here. Can be UNC path like \\MyServer\MyFolder"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'Walk through each file in this folder collection.
'If it is older than 3 weeks (21) days, then delete it.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
set Message="Deleting File " & oFile.name
LogMessageToFile(Message)
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
Sub LogMessageToFile(Message)
Dim FilePath
Const ForWriting =2
set FilePath="C:\Log.txt"
Set oFS = Server.CreateObject("Scripting.FileSystemObject")
If oFS.FileExists(FilePath) = True Then
Set oFSFile = oFS.OpenTextFile(FilePath,ForWriting,True)
oFSFile.Write(Message)
End If
oFSFile.Close
Set oFSFile = Nothing
Set oFS = Nothing
End Sub
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 12, 2009 at 12:41 am
Thanks Osama,
With this Script Iam able to delete backup file 'n' days older But its not logging what it is deleting in C:\log.txt as you mentioned in the script. I used this script as it is But its not logging the details.
Could you please tell me where Iam going wrong?
February 12, 2009 at 11:30 am
kotlas7 (2/12/2009)
Thanks Osama,With this Script Iam able to delete backup file 'n' days older But its not logging what it is deleting in C:\log.txt as you mentioned in the script. I used this script as it is But its not logging the details.
Could you please tell me where Iam going wrong?
try this ......
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 = 21
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "FolderName here. Can be UNC path like \\MyServer\MyFolder"
set oFolder = oFSO.GetFolder("C:\reports\")
set oFileCollection = oFolder.Files
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("C:\Log.txt", ForAppending, True)
'Walk through each file in this folder collection.
'If it is older than 3 weeks (21) days, then delete it.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
objTextFile.WriteLine "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
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 12, 2009 at 12:38 pm
Hi Osama,
The 2nd script is NOT deleting the Backup files But it is creating an empty log.txt file in C:\log.txt.
Could you please make it work to delete backup files as well as generating log file
Thanks in Advance
February 12, 2009 at 1:24 pm
kotlas7 (2/12/2009)
Hi Osama,The 2nd script is NOT deleting the Backup files But it is creating an empty log.txt file in C:\log.txt.
Could you please make it work to delete backup files as well as generating log file
Thanks in Advance
The script is running fine at my end....does backup files meet the 3 week old criteria....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 12, 2009 at 4:42 pm
Thanks Osama,
Its working after changing set oFolder = oFSO.GetFolder("C:\reports\") to set oFolder = oFSO.GetFolder(sDirectoryPath).Now able to delete the backupfile and also its logging what files it is deleting.
One more question here:
In the log file I would like to get the date.What should I add to the script to get the date in log.txt file?
February 12, 2009 at 8:52 pm
kotlas7 (2/12/2009)
Thanks Osama,Its working after changing set oFolder = oFSO.GetFolder("C:\reports\") to set oFolder = oFSO.GetFolder(sDirectoryPath).Now able to delete the backupfile and also its logging what files it is deleting.
One more question here:
In the log file I would like to get the date.What should I add to the script to get the date in log.txt file?
objTextFile.WriteLine "Deleting File " & oFile.name & Date
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 13, 2009 at 3:39 pm
Thanks Osama,
Now evrything is fine. But Iam confused in scheduling the jobs for deleting .bak files and .trn files using this VBSCRIPT. Here is the scenario:
Hi,
We have scheduled full backups every night 2:00am, differential at every 6hrs and log backup every 1 hr.
Full backup goes to Full_BACKUP folder, Differential backup goes to DIFF_BACKUP folder and Log backup goes to LOG_BACKUP folder.
Now using this VBSCRIPT, We would like to delte backup files olderthan 2 days. ie .bak files in Full_BACKUP folder n DIFF_BACKUP folder and .trn files in LOG_BACKUP.
The Full_BACKUP folder,DIFF_BACKUP folder and LOG_BACKUP folder are inside D drive like
D:\Backup->FUll_backup
->Diff_backup
->Log_backup.
Now, Using this VBSCRIPT, Do I need to create 3 jobs each for one backup folder to delete files in them or Can I create 1 job and add 3 steps in it one for each backup folder?
How can we ahieve this in a best possible way?
We have 3 jobs for each type of backup.
Please give me your suggestions
Thanks
February 14, 2009 at 1:11 am
Now, Using this VBSCRIPT, Do I need to create 3 jobs each for one backup folder to delete files in them or Can I create 1 job and add 3 steps in it one for each backup folder?
you can have single job with 3 steps and control the behaviour of steps by setting "On success" and "On failure" properties as per your needs.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 20, 2009 at 10:35 am
Is there a way to use this to delete files in sub-directories as well?
July 20, 2009 at 11:45 am
You can use the VBScript discussed here to delete files in sub-directories too. I'm using this with out any issues..
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply