Couple of days ago I’ve been asked by a customer to preserve the Reporting Services trace log files. Reporting Services provides three trace log files, which are located at Microsoft SQL ServerReporting ServicesLogFiles. Trace logs are created daily, starting with the first entry that occurs after midnight (local time). The local time of the computer is used for naming the trace log. By default, trace logs are deleted after fourteen days.
The solution is quite simple. In SQL Server 2000, I created a scheduled job that uses an ActiveX script task. In the VBScript, I use the FileSystemObject object that comes with Script Runtime library (check the documentation). All RS trace log have the date of creation included in the name. The only trick is to extract the date from the file name, compare it with the actual date, and finally move or copy the file to some destination folder, before it is deleted. Here I am including the script.
Option Explicit
Dim objFSO, strFolder, objFolder, objFile, colFiles
Dim strFileDate, dtFileDate, strForDelete
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
strFolder = “C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesLogFiles”
Set objFolder = objFSO.GetFolder(strFolder)
Set colFiles = objFolder.Files
If colFiles.Count > 0 Then
For Each objFile in colFiles
strFileDate=Mid(objFile.Name,InStr(objFile.Name,”.log”)-13,4) & “-” & _
Mid(objFile.Name,InStr(objFile.Name,”.log”)-19,2) & “-” & _
Mid(objFile.Name,InStr(objFile.Name,”.log”)-16,2)
dtFileDate=CDate(strFileDate)
If dtFileDate < Date – 7 Then
objFile.Move(“C:Temp” & objFile.Name) ‘Insert destination folder here
End If
Next
Else
WScript.Echo(“No files to move in the log folder ” & objFolder.Name)
End If
Set objFSO = Nothing
Set objFolder = Nothing
Set colFiles = Nothing