Recreate Reporting Database

Issue

Is there a way to clear out all data from the TotalAgility_Reporting database to start over?

Solution

To reset all processed reporting data, the TotalAgility_Reporting database can be recreated, which is the topic of this article.  To instead reset a backlog of unprocessed reporting data, see Reset Reporting Staging Database

To avoid needing to update database connection strings across the whole installation, it is ideal to create the new database with the same name as the original.  One way to accomplish this is to drop or renaming the original database before creating the new one.  Another way is to initially create the new database with a different name, then change the names of both when ready to switch.

Preparation: TotalAgility_Reporting Database

The TotalAgility Reporting Service writes to the TotalAgility_Reporting database, and when using Insight/KAFTA, the Insight services read from it.  These all need to be stopped until the new database is available.

Creating and Initializing TotalAgility_Reporting Tables

After a DBA creates a new database, the tables can be created and initialized by running the scripts that are included in the KTA install source.

{Install Source}\TotalAgility\TotalAgilityInstall\DatabaseScripts\SQL Server\Reporting\Kofax.Reporting.Analytics.SQL_TablesCreate.sql

{Install Source}\TotalAgility\TotalAgilityInstall\DatabaseScripts\SQL Server\Reporting\Kofax Kofax.Reporting.Analytics.SQL_Initialize.sql

Before Restarting Services

Often if this database is being recreated, then so is the staging database (see Reset Reporting Staging Database).  However if you are recreating only the main Reporting database, and keeping a staging database that has unprocessed reporting data, then run the following update on the TotalAgility_Reporting_Staging database, after the Reporting database has been recreated, but before the reporting service is started.  This allows the data remaining in the staging database to properly process into the newly created main database.