Logs

Forefront TMG generates logs for monitoring and analyzing the status of the following components:

The Microsoft Firewall service.
The Forefront TMG Web proxy.

Each component has a separate log, which is represented by an FPCLog object in the FPCLogs collection for the array.

Forefront TMG can be configured to save the information in the Firewall service and Web proxy logs in the following formats:

Text log files in the World Wide Web Consortium (W3C) extended file format.
Text log files in the Forefront TMG file format.
A Microsoft SQL Server Express 2008 log.
A SQL Server database using the Microsoft OLE DB provider.

For information about the log fields available for all three logging components, see Log Fields.

Copying and Moving SQL Server Express 2008 Log Files

If you need to copy SQL Server Express 2008 log files from one location to another, or to move the files to another server, you must first detach the database from the current server. You should never detach a database that is currently in use. One way to determine whether a database is in use is to verify that the date included in the file name has past or that a database with a higher number exists for the current date. Another way is to enter the following lines at command prompts:

OSQL -S computer_name\MSFW -E

sp_who2

This will list all SQL Server Express databases that currently have open connections.

To detach a database from a server, enter the following lines at command prompts:

OSQL -S computer_name\MSFW -E

sp_detach_db database_name

quit

Note that database_name is the name of the .mdf and .ldf files without their extensions.

The following Microsoft Visual Basic® Scripting Edition (VBScript) script can also be used to detach a database from a server:

Dim serverName	' A String
Dim dbName		' A String
Dim cn			' An ADODB Connection object
Dim cmd			 ' An ADODB Command object
Set args = WScript.Arguments
If args.Count < 2 Then
	WScript.Echo("Usage: WScript.ScriptName ServerName DbName")
	WScript.Quit(0)
Else
	serverName = args.Item(0)
	dbName = args.Item(1)	 
End If
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionTimeout = 25
cn.Provider = "SQLOLEDB"
cn.Properties("Data Source").Value = serverName & "\msfw"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = "sp_detach_db " & dbName
Set cmd.ActiveConnection = cn
WScript.Echo(cmd.CommandText)
cmd.Execute
WScript.Echo "Done!"

Now you can copy the .mdf and .ldf files to any other location.

We recommend that you reattach the database to the server after you copy the files and let Forefront TMG manage the deletion of these files. If you want to view the information in these files in the log viewer or include the information in future reports, you must reattach the database to the server. To reattach a database to a server, enter the following lines at command prompts:

OSQL -S computer_name\MSFW -E

Sp_attach_db @dbname='database_name', @filename1='full_path_to_mdf_file', @filename2='full_path_to_ldf_file'

quit

The following VBScript script can also be used to reattach a pair of .mdf and .ldf database files to a server:

Dim serverName	' A String
Dim dbName		' A String
Dim cn			' An ADODB Connection object
Dim pathToFiles	 ' A String
Dim cmd			 ' An ADODB Command object
Dim pathToMdfFile   ' A String
Dim pathToLdfFIle   ' A String
Set args = WScript.Arguments
If args.Count < 3 Then
	WScript.Echo("Usage: WScript.ScriptName ServerName DbName PathToFiles")
	WScript.Quit(0)
Else
	serverName = args.Item(0)
	dbName = args.Item(1)
	pathToFiles = args.Item(2)
	pathToMdfFile = pathToFiles & "\" + dbName + ".mdf"
	pathToLdfFile = pathToFiles & "\" + dbName + ".ldf"
	WScript.Echo("mdf: " & pathToMdfFile )
	WScript.Echo("ldf: " & pathToLdfFile )
End If
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionTimeout = 25
cn.Provider = "SQLOLEDB"
cn.Properties("Data Source").Value = serverName & "\msfw"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = "sp_attach_db @dbname=" & dbName & ",@filename1='" _
	& pathToMdfFile & "'" & ",@filename2='" & pathToLdfFile & "'"
Set cmd.ActiveConnection = cn
WScript.Echo(cmd.CommandText)
cmd.Execute
WScript.Echo "Done!"

Forefront TMG automatically removes database files that are no longer needed, but you can delete database files manually after you detach the database from the current server. You can also delete database files by entering the following lines at command prompts:

OSQL -S computer_name\MSFW -E

drop database database_name

quit

The following VBScript script can also be used to delete database files:

Dim serverName	' A String
Dim dbName		' A String
Dim cn			' An ADODB Connection object
Dim cmd			 ' An ADODB Command object
Set args = WScript.Arguments
If args.Count < 2 Then
	WScript.Echo("Usage: WScript.ScriptName ServerName DbName")
	WScript.Quit(0)
Else
	serverName = args.Item(0)
	dbName = args.Item(1)	 
End If
Set cn = CreateObject("ADODB.Connection")
cn.ConnectionTimeout = 25
cn.Provider = "SQLOLEDB"
cn.Properties("Data Source").Value = serverName & "\msfw"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = "drop database " & dbName
Set cmd.ActiveConnection = cn
WScript.Echo(cmd.CommandText)
cmd.Execute
WScript.Echo "Done!"

Send comments about this topic to Microsoft

Build date: 11/30/2009