Task 2.4.1(b): Recover Log Database, SQL Server Alternative

Note: It is recommended that you contact badgertracs@wi.dot.gov to help you assess your agency’s situation. There may be less drastic options available depending on the extent of the damage.

Purpose: Recreate the TraCS Log database when it is destroyed beyond repair and no backups exist.

Requirements: TraCS Installation files, access to field unit with TraCS mobile installed.

In SQL Server Management Studio (SSMS)
1. Create the tracs_log database.

USE master
GO
CREATE DATABASE tracs_log
GO
use tracs_log
GO
DECLARE @sql1 VARCHAR(1000)
SELECT @sql1='ALTER DATABASE '+quotename(db_name())+' SET ALLOW_SNAPSHOT_ISOLATION ON
			 ALTER DATABASE '+quotename(db_name())+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
			 ALTER DATABASE '+quotename(db_name())+' SET read_committed_snapshot ON
			 ALTER DATABASE '+quotename(db_name())+' SET MULTI_USER'
exec(@sql1)

Create the needed tables in the tracs_log database using the “log” SQL script. The script will be in c:\programdata\tracs\Baseline Files\Scripts\ Baseline Log Total SQLServer.sql

Note: a common error is to run the script against the default database, which is typically not the tracs_data database. Either change the default database in SSMS to tracs_data or add the following lines to the script before running it—

USE [tracs_log]
Go

Run the BTDBLog distribution file. (This adds state access levels.)
1. In the configuration manager, select the update tab.
2. Click the run distribution button.
3. Navigate to the C:\ProgramData\TraCS\Distribution\Templates folder
4. Select BTDBLog.dist.exml