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