Database 자동 생성 스크립트

제약 항목

1. 로그인 계정 고정(자동생성 - DB 명칭, 소문자)

  - 패스워드 고정

2. 대상 폴더 고정

3. Database 호환성 수준 MSSQL 2008로 고정

4. 생성 DB의 Owner는 1번에서 생성되는 계정으로 사용

* 필요 하다면 매개변수를 외부에서 받아서 사용하시면 됩니다.

-------------------------------------------------------------------------------------------------------------

CREATE PROC [dbo].[DB_CREATE]

(

@DB_NAME NVARCHAR(100)

)

AS

BEGIN

IF ISNULL(@DB_NAME,'') = ''

RETURN -1;

 

DECLARE @LOG_NAME NVARCHAR(104);

DECLARE @QRY NVARCHAR(MAX);

DECLARE @DB_FOLDER NVARCHAR(1000);

DECLARE @LOGINNAME NVARCHAR(100);

DECLARE @DEFAULT_FOLDER NVARCHAR(1000);

DECLARE @PASSWORDS NVARCHAR(1000);

SET @DB_NAME = UPPER(@DB_NAME)

SET @LOGINNAME = LOWER(@DB_NAME)

SET @LOG_NAME = @DB_NAME+N'_Log'

SET @PASSWORDS = N'비밀번호'

SET @DEFAULT_FOLDER = N'D:\DataBase\Project\'; -- 마지막 \ 표시 주의

SET @DB_FOLDER =  @DEFAULT_FOLDER + @DB_NAME

EXECUTE master.dbo.xp_create_subdir @DB_FOLDER  -- 폴더 생성

SET @QRY = '

CREATE LOGIN ['+@LOGINNAME+'] WITH PASSWORD=N'''+@PASSWORDS+'''

, DEFAULT_DATABASE=[master]

, DEFAULT_LANGUAGE=[한국어]

, CHECK_EXPIRATION=OFF

, CHECK_POLICY=OFF;

CREATE DATABASE ['+@DB_NAME+'] 

CONTAINMENT = NONE 

ON  PRIMARY 

( NAME = N'''+@DB_NAME+''', FILENAME = N'''+@DB_FOLDER+'\'+@DB_NAME+'.mdf'' , SIZE = 8192KB , FILEGROWTH = 65536KB ) 

LOG ON  

( NAME = N'''+@LOG_NAME+''', FILENAME = N'''+@DB_FOLDER+'\'+@LOG_NAME+'.ldf'' , SIZE = 8192KB , FILEGROWTH = 65536KB ) ;

ALTER DATABASE ['+@DB_NAME+'] SET COMPATIBILITY_LEVEL = 100;

ALTER DATABASE ['+@DB_NAME+'] SET ANSI_NULL_DEFAULT OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET ANSI_NULLS OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET ANSI_PADDING OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET ANSI_WARNINGS OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET ARITHABORT OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET AUTO_CLOSE OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET AUTO_SHRINK OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF);

ALTER DATABASE ['+@DB_NAME+'] SET AUTO_UPDATE_STATISTICS ON ;

ALTER DATABASE ['+@DB_NAME+'] SET CURSOR_CLOSE_ON_COMMIT OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET CURSOR_DEFAULT  GLOBAL ;

ALTER DATABASE ['+@DB_NAME+'] SET CONCAT_NULL_YIELDS_NULL OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET NUMERIC_ROUNDABORT OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET QUOTED_IDENTIFIER OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET RECURSIVE_TRIGGERS OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET  DISABLE_BROKER ;

ALTER DATABASE ['+@DB_NAME+'] SET AUTO_UPDATE_STATISTICS_ASYNC OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET DATE_CORRELATION_OPTIMIZATION OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET PARAMETERIZATION SIMPLE ;

ALTER DATABASE ['+@DB_NAME+'] SET READ_COMMITTED_SNAPSHOT OFF ;

ALTER DATABASE ['+@DB_NAME+'] SET  READ_WRITE ;

ALTER DATABASE ['+@DB_NAME+'] SET RECOVERY FULL ;

ALTER DATABASE ['+@DB_NAME+'] SET  MULTI_USER ;

ALTER DATABASE ['+@DB_NAME+'] SET PAGE_VERIFY CHECKSUM  ;

ALTER DATABASE ['+@DB_NAME+'] SET TARGET_RECOVERY_TIME = 60 SECONDS ;

ALTER DATABASE ['+@DB_NAME+'] SET DELAYED_DURABILITY = DISABLED ;

EXEC ['+@DB_NAME+'].dbo.sp_changedbowner @loginame = N'''+@LOGINNAME+''', @map = false;

'

Exec sp_executesql @QRY

END;

-------------------------------------------------------------------------------------------------------------