Technical Article

Trim Backup Files


This script runs within a DTS package. It searches the backup directory stored as a global variable in the script and trims all backup files older than some number of days. the script includes logging to the C: drive so you can trace the effects of the script.

You need to create the global variables in the DTS package for DaystoKeep, BackupPath, and DBname.

'  Trim Old Backups
' This script will remove (delete) old backup files that are older
' than a certain date. The number of days that are being kept
' is stored in a global variable "DaysToKeep". This script is designed
' to run once a day, find the most recent backup that is closest to 
' but older than the value in DaysToKeep and remove all files older 
' than this file. 
' The script searches all folders below the backup path given in 
' the global variable "BackupPath" and processes each one separately.
Function Main()
'On Error Resume Next

Dim strDestPath, strNewFile, strSourcePath, strDBName
Dim objobjFSO, objFolder, objFileHandle
Dim strBaseDate, strOldFileHandle, strFromFile, strToFile
Dim objFolderItem
Dim ObjFileItem
Dim objFiles
Dim objTxtFile
Dim dude
Dim iDays

' Get source and destination paths
strSourcePath = DTSGlobalVariables("BackupPath").value
strDays = DTSGlobalVariables("DaystoKeep").value
iDays = 0 - strDays

' Initialize Variables
strDBName = DTSGlobalVariables("DBname").value
strNewFile = "\" & strDBName & ".bak"
strBaseDate = DateAdd( "d" , iDays , Date)

' Create FileSystem Object and then get the Files collection
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Test file
Set objTxtFile = objFSO.CreateTextFile(  "c:\DTS_TrimBUFiles.txt", TRUE)
objTxtFile.writeline( "DTS Trim Backup Files" )
objTxtFile.writeline( " " )
objTxtFile.writeline( "BaseDate:" & strBaseDate )
objTxtFile.writeline( "Date:" & Date )
objTxtFile.writeline( "iDays:" & iDays )
objTxtFile.writeline( "strDays:" & strDays )
objTxtFile.writeline( " " )

Set objFolder = objFSO.GetFolder(strSourcePath)

' Loop through each folder
For Each objFolderItem in objFolder.Subfolders

objTxtFile.writeline( "|")

Set objFiles = objFolderItem.Files

For Each objFileItem in objFiles
objTxtFile.writeline( "|- " & objFileItem.Name & " : "  & objFileItem.dateCreated)
if objFileItem.Datecreated < strBaseDate Then
objTxtFile.writeline( "     delete  ")

' Remove the last backup
objFSO.DeleteFile objFileItem
end if

objTxtFile.writeline( "  " )


' Clean up
Set objFSO = Nothing


Main = DTSTaskExecResult_Success

End Function


1 (1)




1 (1)