Create database

If required, replace all Carillon entries with desired name of the database (for example prod_Carillon). Database settings and files can be modified but file groups created by this script are required.

USE [master]
GO

IF NOT EXISTS (SELECT * FROM [master].[sys].[databases] WHERE name = N'Carillon')
BEGIN
 PRINT 'Create database: Carillon'
 CREATE DATABASE [Carillon] COLLATE SQL_Latin1_General_CP1_CI_AS
END
GO

IF NOT EXISTS (SELECT * FROM [Carillon].[sys].[objects] WHERE name = N'tableSystemValues' AND type in (N'U'))
BEGIN
 PRINT 'Set database settings'
 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
 BEGIN
  EXEC [Carillon].[dbo].[sp_fulltext_database] @action = 'disable'
 END
 ALTER DATABASE [Carillon] SET ANSI_NULL_DEFAULT OFF
 ALTER DATABASE [Carillon] SET ANSI_NULLS OFF
 ALTER DATABASE [Carillon] SET ANSI_PADDING OFF
 ALTER DATABASE [Carillon] SET ANSI_WARNINGS OFF
 ALTER DATABASE [Carillon] SET ARITHABORT OFF
 ALTER DATABASE [Carillon] SET AUTO_CLOSE OFF
 ALTER DATABASE [Carillon] SET AUTO_CREATE_STATISTICS ON
 ALTER DATABASE [Carillon] SET AUTO_SHRINK OFF
 ALTER DATABASE [Carillon] SET AUTO_UPDATE_STATISTICS ON
 ALTER DATABASE [Carillon] SET CURSOR_CLOSE_ON_COMMIT OFF
 ALTER DATABASE [Carillon] SET CURSOR_DEFAULT  GLOBAL
 ALTER DATABASE [Carillon] SET CONCAT_NULL_YIELDS_NULL OFF
 ALTER DATABASE [Carillon] SET NUMERIC_ROUNDABORT OFF
 ALTER DATABASE [Carillon] SET QUOTED_IDENTIFIER OFF
 ALTER DATABASE [Carillon] SET RECURSIVE_TRIGGERS OFF
 ALTER DATABASE [Carillon] SET DISABLE_BROKER
 ALTER DATABASE [Carillon] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
 ALTER DATABASE [Carillon] SET DATE_CORRELATION_OPTIMIZATION OFF
 ALTER DATABASE [Carillon] SET TRUSTWORTHY OFF
 ALTER DATABASE [Carillon] SET ALLOW_SNAPSHOT_ISOLATION OFF
 ALTER DATABASE [Carillon] SET PARAMETERIZATION SIMPLE
 ALTER DATABASE [Carillon] SET READ_WRITE
 ALTER DATABASE [Carillon] SET RECOVERY FULL
 ALTER DATABASE [Carillon] SET MULTI_USER
 ALTER DATABASE [Carillon] SET PAGE_VERIFY CHECKSUM  
 ALTER DATABASE [Carillon] SET DB_CHAINING OFF
END
GO

IF NOT EXISTS (SELECT * FROM [Carillon].[sys].[filegroups] WHERE name = N'Logging')
BEGIN
 PRINT 'Create filegroup: Logging'
 ALTER DATABASE [Carillon] ADD FILEGROUP [Logging]
END
GO

IF NOT EXISTS (SELECT * FROM [Carillon].[sys].[filegroups] WHERE name = N'History')
BEGIN
 PRINT 'Create filegroup: History'
 ALTER DATABASE [Carillon] ADD FILEGROUP [History]
END
GO

IF NOT EXISTS (SELECT * FROM [Carillon].[sys].[filegroups] WHERE name = N'Reports')
BEGIN
 PRINT 'Create filegroup: Reports'
 ALTER DATABASE [Carillon] ADD FILEGROUP [Reports]
END
GO

IF EXISTS (SELECT * FROM [Carillon].[sys].[database_files] WHERE name = N'Carillon_log' AND size < 3200)
BEGIN
 PRINT 'Modify file: Carillon_log'
 ALTER DATABASE [Carillon] MODIFY FILE ( NAME = N'Carillon_log', SIZE = 25MB , MAXSIZE = UNLIMITED, FILEGROWTH = 25MB )
END
GO

IF EXISTS (SELECT * FROM [Carillon].[sys].[database_files] WHERE name = N'Carillon' AND size < 6400)
BEGIN
 PRINT 'Modify file: Carillon'
 ALTER DATABASE [Carillon] MODIFY FILE ( NAME = N'Carillon', SIZE = 50MB , MAXSIZE = UNLIMITED , FILEGROWTH = 50MB )
END
GO

DECLARE @stringFolder varchar(500)
DECLARE @stringAddFile nvarchar(1000)

SELECT @stringFolder = REVERSE(SUBSTRING(REVERSE(physical_name), CHARINDEX('\', REVERSE(physical_name)), 500)) FROM [Carillon].[sys].[database_files] WHERE name = N'Carillon'

IF NOT EXISTS (SELECT * FROM [Carillon].[sys].[database_files] WHERE name = N'Carillon_logging1')
BEGIN
 PRINT 'Create database file: Carillon_logging1'
 SET @stringAddFile = N'ALTER DATABASE [Carillon] ADD FILE ( NAME = N''Carillon_logging1'', FILENAME = N''' + @stringFolder + 'Carillon_logging1.ndf'', SIZE = 25MB , MAXSIZE = UNLIMITED , FILEGROWTH = 25MB ) TO FILEGROUP [Logging]'
 EXEC (@stringAddFile)
END

IF NOT EXISTS (SELECT * FROM [Carillon].[sys].[database_files] WHERE name = N'Carillon_history1')
BEGIN
 PRINT 'Create database file: Carillon_history1'
 SET @stringAddFile = N'ALTER DATABASE [Carillon] ADD FILE ( NAME = N''Carillon_history1'', FILENAME = N''' + @stringFolder + 'Carillon_history1.ndf'', SIZE = 100MB , MAXSIZE = UNLIMITED , FILEGROWTH = 50MB ) TO FILEGROUP [History]'
 EXEC (@stringAddFile)
END

IF NOT EXISTS (SELECT * FROM [Carillon].[sys].[database_files] WHERE name = N'Carillon_reports1')
BEGIN
 PRINT 'Create database file: Carillon_reports1'
 SET @stringAddFile = N'ALTER DATABASE [Carillon] ADD FILE ( NAME = N''Carillon_reports1'', FILENAME = N''' + @stringFolder + 'Carillon_reports1.ndf'', SIZE = 100MB , MAXSIZE = UNLIMITED , FILEGROWTH = 50MB ) TO FILEGROUP [Reports]'
 EXEC (@stringAddFile)
END

USE [Carillon]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tableSystemValues]') AND type in (N'U'))
BEGIN
PRINT ' Table: [dbo].[tableSystemValues]'
CREATE TABLE [dbo].[tableSystemValues](
 [integerSystemValueID] [int] IDENTITY(1,1) NOT NULL,
 [stringValueTitle] [varchar](50) NOT NULL,
 [stringValue] [nvarchar](4000) NULL,
 [integerValue] [bigint] NULL,
 [binaryValue] [varbinary](256) NULL,
 [datetimeValue] [datetime] NULL,
 [modified] [timestamp] NOT NULL,
 CONSTRAINT [PK_tableSystemValues] PRIMARY KEY CLUSTERED
(
 [integerSystemValueID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF (SELECT COUNT(*) FROM [dbo].[tableSystemValues] WHERE stringValueTitle = 'Application') = 0
 INSERT INTO [dbo].[tableSystemValues](stringValueTitle, stringValue) VALUES ('Application', 'Carillon')
ELSE
 UPDATE [dbo].[tableSystemValues] SET stringValue = 'Carillon' WHERE stringValueTitle = 'Application' AND stringValue <> 'Carillon'