Overview

ProblemTracker interfaces with either Microsoft Access or Microsoft SQL Server databases using a standard ODBC interface. A Database Administration Utility is included with ProblemTracker to provide some basic operations (e.g. initialization) which may be required in order to use a database with ProblemTracker. However, backups and other database administration should be performed using the tools that come with the database.

Microsoft Access Databases

By default ProblemTracker uses a Microsoft Access database file located at:

installDir/versionDir/Database/Bugtrack.mdb

where installDir is the directory where ProblemTracker was installed, and versionDir is the content subdirectory of the ProblemTracker workgroup that was installed. There is one database per workgroup.

It is recommended that you back up the database file on a regular basis (e.g. daily) and at a time when it is highly unlikely to be in use (e.g. early morning when backups are typically run). As with any other Windows file, if the file is open, it cannot be backed up until it is closed. ProblemTracker only accesses the database (opens the file) briefly to handle each request (it is only opened between the time a user clicks on a link or button and the requested web page is returned, it is not left open).

To back up your Access database, copy the file installDir/versionDir/Database/Bugtrack.mdb (as described above) for each workgroup (ptdev, ptweb, ptdev2, etc.). You also need to back up the Attachments folder which is located at installDir/versionDir/Attachments for each workgroup.

Over time this file may become quite large. You can attempt to compact this file by either using Microsoft Access, or the ODBC Database Administrator (in the Control Panel). It is strongly recommended that you backup the file before attempting this operation.

MS SQL Server Databases

If you are using ProblemTracker with a database other than Microsoft Access, please refer to the documentation for your database on recommended administration procedures, such as regular scheduled backups, etc.

There is one SQL Server database for each ProblemTracker workgroup (e.g. ptdev, ptweb, ptdev2, ptdev3, ptdev4). ProblemTracker uses a login with the name PROBLEMTRACKER to access these databases for ProblemTracker operations.

Please refer to your SQL Server manual for instructions on how to back up your database properly.

Direct Database Edits are Not Supported

Although ProblemTracker uses a standard database, it is recommended that you do not directly modify (or even open) the database(s). Doing so may result in serious and perhaps unrecoverable damage to your ProblemTracker data. Directly accessing the database may also lock users out of the system. Support is not provided for ProblemTracker installations in which the database(s) have been modified using anything other than the (unmodified) ProblemTracker web pages that are shipped with the product (unless you were directed to do so by NetResults Technical Support or NetResults Professional Services). If NetResults Technical Support discovers that your data has been directly modified (or your ProblemTracker web pages have been modified), they may require that you reinstall the product and reinitialize the database(s) before providing any further support.

Direct modification of a ProblemTracker database or source HTML pages by users is not supported by NetResults.

The Database Administration Utility

To start the Database Administration Utility for a particular workgroup, select
Start->Programs->NetResults ProblemTracker->workgroup->Database Admin Page
on the machine on which you installed ProblemTracker. Each workgroup has its own Database Admin Utility web page. The following options are available:

Initialize Database
For databases other than MS Access, it is necessary to first initialize the database before using ProblemTracker the first time. This operation creates the necessary database tables and populates the database with the initial data.

Export Database
Allows you to export the entire database in the form of comma separate text files (CSV). Useful to create a snapshot of the database at any point in time, or to generate data in a format usable by third party utilities.

Delete Database
Completely deletes the data and tables from the database. Should not be used without the supervision of NetResults Technical Support.

Data Migration
This function is currently only used during data migration from one database type to another.

Create Schema
This function is currently only used during data migration from one database type to another.

Import Database
Allows you to import an entire database in the form of comma separated text files (CSV). Due to data integrity issues spanning multiple tables, this feature should not be used without the supervision of NetResults Technical Support or Professional Services. Doing so will most likely result in a non-functional database (even though it may appear to work for a little while).

Initializing Databases

If you are using a database other than MS Access, or are using a blank MS Access database instead of one of the provided template databases, you must first initialize the database. Initializing the database creates all the database tables and loads the initial data.

The format of the database tables and the initial data are located in a subdirectory named "Importer\ProblemTracker30\Import" below the installation directory (ptdev, ptweb, etc.). The file db_def.csv lists all of the tables, and the format of each table is described by a file located in the Def subdirectory. The initial data is located in the Init subdirectory.

To initialize the database, select the database type from the pulldown, and click on the "Initialize Database" button.

Exporting Databases

The export operation exports the entire contents of the database to comma separate text files (CSV). The files are written to a subdirectory named "Importer\ProblemTracker30\Export\Data" below the installation directory (ptdev, ptweb, etc.). One file is generated for each table.

To export the database, select the database type from the pulldown, and click on the "Export Database" button.

Deleting Databases

The Delete operation removes all the data and database tables from the database. Only use this operation if you do not care about the existing data in the database, and have a way to restore your database to a desired state.

To delete the database, select the database type from the pulldown, and click on the "Delete Database" button.

Restoring A Database From A Previous Export

It may be useful to restore a database using the files generated from a previous Export operation.

To restore the database, follow these steps:

1. Locate the directory named "Importer\ProblemTracker30\Import\Init" below the installation directory for the workgroup (C:\Inetpub\wwwroot\ptdev, C:\Inetpub\wwwroot\ptweb, etc.).
2. Locate the directory that corresponds to the workgroup (ptdev, ptweb, etc.) below the Init directory.
3. Save any existing files in this directory somewhere safe.
4. Copy the exported files to this directory.
5. Select the database type, and click on the Delete Database button to clean out the existing data. Note: this will delete all existing problem reports, workflow configuration, user configuration, etc. from the existing database.
6. Select the database type and click on the Initialize Database button.
7. Restore the original files to the "Importer\ProblemTracker30\Import\Init" directory.