2.CreateTheDb.sql

-- START SCRIPT 2.CreateTheDb.sql

DECLARE @DataPath nvarchar(200)
set @DataPath = N'$(DataPath)'
SELECT 'USING THE FOLLOWING DataPath ' + @DataPath

DECLARE @DbName nvarchar(200)
set @DbName = N'$(DbName)'
SELECT 'USING THE FOLLOWING DbName ' + @DbName


DECLARE @CurrentDate nvarchar(200)
set @CurrentDate = N'$(CurrentDate)'
SELECT 'USING THE FOLLOWING CurrentDate ' + @CurrentDate



DECLARE @StrSql nvarchar(max)
set @StrSql = '

/****** Object: Database [' + @DbName + '] Script Date: ' + @CurrentDate + ' ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N''' + @DbName + ''')
DROP DATABASE [' + @DbName + '] ;'

SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

set @StrSql =
'/****** Object: Database [' + @DbName + '] Script Date: ' + @CurrentDate + ' ******/
CREATE DATABASE [' + @DbName + '] ON PRIMARY
( NAME = ''[' + @DbName + ']'', FILENAME = ''' + @DataPath + @DbName + '.mdf'' , SIZE = 34816KB ,
MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON ( NAME = ''[' + @DbName + ']_log'',
FILENAME = ''' + @DataPath + @DbName + '.ldf'' ,
SIZE = 164544KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) ;'


SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

set @StrSql =
'IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))
begin
EXEC [' + @DbName + '].[dbo].[sp_fulltext_database] @action = ''enable''
end ;'


SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

set @StrSql =
'ALTER DATABASE [' + @DbName + '] SET ANSI_NULL_DEFAULT OFF ;
ALTER DATABASE [' + @DbName + '] SET ANSI_NULLS OFF ;
ALTER DATABASE [' + @DbName + '] SET ANSI_WARNINGS OFF ;
ALTER DATABASE [' + @DbName + '] SET ARITHABORT OFF ;'



SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

set @StrSql =
'ALTER DATABASE [' + @DbName + '] SET AUTO_CLOSE ON ;
ALTER DATABASE [' + @DbName + '] SET AUTO_CREATE_STATISTICS ON ;
ALTER DATABASE [' + @DbName + '] SET AUTO_SHRINK ON ;
ALTER DATABASE [' + @DbName + '] SET AUTO_UPDATE_STATISTICS ON ;'


SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

set @StrSql =
'ALTER DATABASE [' + @DbName + '] SET CURSOR_CLOSE_ON_COMMIT ON ;
ALTER DATABASE [' + @DbName + '] SET CURSOR_DEFAULT GLOBAL ;
ALTER DATABASE [' + @DbName + '] SET CONCAT_NULL_YIELDS_NULL OFF ;
ALTER DATABASE [' + @DbName + '] SET NUMERIC_ROUNDABORT OFF ;'

SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

set @StrSql =
'ALTER DATABASE [' + @DbName + '] SET QUOTED_IDENTIFIER OFF ;
ALTER DATABASE [' + @DbName + '] SET RECURSIVE_TRIGGERS ON ;
ALTER DATABASE [' + @DbName + '] SET DISABLE_BROKER ;
ALTER DATABASE [' + @DbName + '] SET AUTO_UPDATE_STATISTICS_ASYNC ON ;'

SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

set @StrSql =
'ALTER DATABASE [' + @DbName + '] SET DATE_CORRELATION_OPTIMIZATION OFF ;
ALTER DATABASE [' + @DbName + '] SET TRUSTWORTHY OFF ;
ALTER DATABASE [' + @DbName + '] SET ALLOW_SNAPSHOT_ISOLATION OFF ;
ALTER DATABASE [' + @DbName + '] SET PARAMETERIZATION SIMPLE ;'

SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

set @StrSql =
'ALTER DATABASE [' + @DbName + '] SET READ_COMMITTED_SNAPSHOT OFF ;
ALTER DATABASE [' + @DbName + '] SET READ_WRITE ;
ALTER DATABASE [' + @DbName + '] SET RECOVERY FULL ;
ALTER DATABASE [' + @DbName + '] SET MULTI_USER ;'

SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

set @StrSql =

'ALTER DATABASE [' + @DbName + '] SET PAGE_VERIFY CHECKSUM ;
ALTER DATABASE [' + @DbName + '] SET DB_CHAINING OFF ; '

SELECT 'CREATING THE DB WITH THE FOLLOWING QUERY :' + @StrSql
EXEC sp_executesql @query = @StrSql

-- END SCRIPT 2.CreateTheDb.sql