How to create database in Microsoft SQL Server Express 2008

Below are steps to create IntegraXor database in Microsoft SQL Server Express 2008, replace “project_id” with desired name in the steps:

1. Create a folder for database storage, for instance “c:\ecava\database\project_id”.
2. Execute the following script in SQL Server Management Studio to create the database:
   

   USE [master]
   GO
   CREATE DATABASE [project_id] ON PRIMARY
   ( NAME = N'project_id', FILENAME = N'C:\Ecava\Database\project_id\project_id.mdf' , SIZE = 266240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
   LOG ON
   ( NAME = N'project_id_log', FILENAME = N'C:\Ecava\Database\project_id\project_id.ldf' , SIZE = 219264KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
   COLLATE SQL_Latin1_General_CP1_CI_AS
   GO

3. Lastly execute the following script to create the needed tables:
   

   -----------------------------------------------------------------------
   -- table
   -----------------------------------------------------------------------

   --drop table alarm;
   CREATE TABLE alarm (
   time_stamp datetime NOT NULL,
   event_id int NOT NULL,
   tag_id int NOT NULL,
   tag_name varchar(64),
   group_name varchar(64),
   description varchar(255),
   state int,
   data_type int,
   nvalue float,
   old_nvalue float,
   svalue varchar(255),
   old_svalue varchar(255),
   [message] varchar(255),
   inactive_timestamp datetime,
   ack_timestamp datetime,
   ack_user varchar(64),
   constraint alarm_pk PRIMARY KEY (time_stamp, event_id, tag_id)
   );

   CREATE INDEX all_alarm ON alarm (state, ack_timestamp);
   CREATE INDEX updated_alarm ON alarm (state, ack_timestamp, time_stamp, inactive_timestamp);
   create index purge_alarm on alarm (time_stamp);

   --drop table audit;
   CREATE TABLE audit (
   time_stamp datetime NOT NULL,
   event_id int NOT NULL,
   tag_id int NOT NULL,
   tag_name varchar(64),
   group_name varchar(64),
   description varchar(255),
   state int,
   data_type int,
   nvalue float,
   old_nvalue float,
   svalue varchar(255),
   old_svalue varchar(255),
   [message] varchar(255),
   inactive_timestamp datetime,
   ack_timestamp datetime,
   ack_user varchar(64),
   constraint audit_pk PRIMARY KEY (time_stamp, event_id, tag_id)
   );

   CREATE INDEX all_audit ON audit (state, ack_timestamp);
   CREATE INDEX updated_audit ON audit (state, ack_timestamp, time_stamp, inactive_timestamp);
   create index purge_audit on audit (time_stamp);

   --drop table [log];
   CREATE TABLE [log] (
   time_stamp datetime NOT NULL,
   tag_name varchar(64) NOT NULL,
   tag_id int,
   state int,
   data_type int,
   nvalue float,
   svalue varchar(255),
   unit varchar(16),
   constraint log_pk PRIMARY KEY (time_stamp, tag_name)
   );

   CREATE INDEX trend ON [log] (nvalue, state, time_stamp, tag_name);
   create index purge_log on [log] (time_stamp);

   --drop table persistence;
   CREATE TABLE persistence (
   time_stamp datetime NOT NULL,
   tag_name varchar(64) NOT NULL,
   tag_id int,
   state int,
   data_type int,
   nvalue float,
   svalue varchar(255),
   unit varchar(16),
   constraint persistence_pk PRIMARY KEY (time_stamp, tag_name)
   );

   CREATE INDEX tag_name ON persistence (tag_name);

   --drop table userdata;
   CREATE TABLE userdata (
   [name] varchar(128) NOT NULL,
   data_type int,
   nvalue float,
   svalue text,
   constraint userdata_pk PRIMARY KEY ([name])
   );
   CREATE INDEX UDIndex ON [userdata] ([name]);