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. Paste and execute the query below to create the tracs_log database. You should use the copy button in the code snippet below.

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)

Make sure to give your agencies TraCS SQL user owner rights to the database.
Just like above in step 1, in SQL Server Management Studio paste and execute the query below to configure your tracs_log database. You should use the copy button in the code snippet below.

Use tracs_log

CREATE TABLE Attachment (
	EmailID NVARCHAR(100),
	AttachmentBinary IMAGE,
	AttachmentFilename NTEXT
	);

CREATE TABLE Email (
	EmailID NVARCHAR(100),
	Status NVARCHAR(20),
	EmailTo NTEXT,
	Cc NTEXT,
	Bcc NTEXT,
	SMTPSetting NVARCHAR(100),
	Subject NVARCHAR(255),
	Body NTEXT,
	SentDate SMALLDATETIME,
	ResendCount NVARCHAR(5),
	CreateDate SMALLDATETIME,
	FailureReason NVARCHAR(255),
	PRIMARY KEY(EmailID)
	);

CREATE TABLE EmailStatus (
	Status NVARCHAR(20),
	Description NVARCHAR(100),
	PRIMARY KEY(Status)
	);

CREATE TABLE SysTransLog (
	Source NVARCHAR(255),
	Process NVARCHAR(255),
	Action NVARCHAR(255),
	MachineID NVARCHAR(255),
	StartDateTime NVARCHAR(255)
	);

CREATE TABLE TraCSLog (
	LogID NVARCHAR(255),
	ID NVARCHAR(255),
	UserID NVARCHAR(255),
	LocationID NVARCHAR(255),
	MachineID NVARCHAR(255),
	FormNumber NVARCHAR(255),
	FormName NVARCHAR(255),
    FormKey NVARCHAR(255),
    ContactKey NVARCHAR(255),
	ContactNumber NVARCHAR(255),
	ContactType NVARCHAR(255),
	LogDate SMALLDATETIME,
	LogTime NVARCHAR(255),
	ActionName NVARCHAR(255),
	Description NVARCHAR(max),
	ConnectionKey NVARCHAR(255),
	LogTable NVARCHAR(255),
	OldValue NVARCHAR(max),
	NewValue NVARCHAR(max),
	LogKey NVARCHAR(36),
	EntryType NVARCHAR(30)
 	);

CREATE TABLE TransLog (
	ID NVARCHAR(255),
	LogDate SMALLDATETIME,
	LogTime NVARCHAR(255),
	ConnectionKey NVARCHAR(255),
	LogTable NVARCHAR(255),
	Description NVARCHAR(max),
	FormNumber NVARCHAR(255),
	FormName NVARCHAR(255),
	FormKey NVARCHAR(255),
	Success NVARCHAR(255),
	BatchID NVARCHAR(255),
	UserID NVARCHAR(255),
	LocationID NVARCHAR(255),
	MachineID NVARCHAR(255),
	InstructionName NVARCHAR(255),
	Type NVARCHAR(255),
	LogType NVARCHAR(255),
	FormFileName NVARCHAR(255),
	ArchiveDate SMALLDATETIME,
	SavedFileName NVARCHAR(255),
	FormDate SMALLDATETIME,
	ArchiveFlag BIT NOT NULL,
	PreExpBackup NVARCHAR(255),
	PostExpBackup NVARCHAR(255),
	PreImportBackup NVARCHAR(255),
	TransCommBackup NVARCHAR(255),
	ActionName NVARCHAR(255)
	);

CREATE TABLE ServiceLog (
	ServiceName NVARCHAR(255),
	Status NVARCHAR(255),
	LastUpdate SMALLDATETIME,
	ShowAlert BIT,
	UpdateIntervalHours INT
	);

CREATE TABLE ServiceLogMessages (
	ServiceName NVARCHAR(255),
	MessageType INT,
	MessageName NVARCHAR(255),
	Message NTEXT,
	UpdateDate SMALLDATETIME,
	Attachment IMAGE,
	ExternalLink NVARCHAR(255),
	Misc1 NVARCHAR(255),
	Misc2 NTEXT,
	ShowMessageAlert BIT,
	TraCSLogID NTEXT
	);

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

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

0 Comments