USE MASTER
GO
--CHECKPOINT 발생하지않도록 INTERVAL 를최대치로변경
EXEC SP_CONFIGURE ' INTERVAL (MIN)', 32767
GO
RECONFIGURE WITH OVERRIDE
GO
--데이터베이스생성
CREATE DATABASE FULLDB ON PRIMARY
( NAME = N'FULLDB', FILENAME = N'D:\FULLDB.MDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'FULLDB_LOG', FILENAME = N'D:\FULLDB_LOG.LDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
CREATE DATABASE SIMPLEDB ON PRIMARY
( NAME = N'SIMPLEDB', FILENAME = N'D:\SIMPLEDB.MDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'SIMPLEDB_LOG', FILENAME = N'D:\SIMPLEDB_LOG.LDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
CREATE DATABASE BULKLOGGEDDB ON PRIMARY
( NAME = N'BULKLOGGEDDB', FILENAME = N'D:\BULKLOGGEDDB.MDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'BULKLOGGEDDB_LOG', FILENAME = N'D:\BULKLOGGEDDB_LOG.LDF' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
--전체복구모델변경
ALTER DATABASE FULLDB SET RECOVERY FULL
GO
--단순복구모델변경
ALTER DATABASE SIMPLEDB SET RECOVERY SIMPLE
GO
--BULK LOGGED 모델데이터베이스변경
ALTER DATABASE BULKLOGGEDDB SET RECOVERY BULK_LOGGED
GO
DBCC SQLPERF(LOGSPACE)
GO
/*
DATABASE NAME LOG SIZE (MB) LOG SPACE USED (%) STATUS
---------------------- ------------- ------------------ -----------
FULLDB 9.992188 2.946638 0
SIMPLEDB 9.992188 2.961298 0
BULKLOGGEDDB 9.992188 2.961298 0
*/
--전체복구모델데이터입력
CREATE TABLE FULLDB.DBO.SQLER (IDX INT PRIMARY KEY IDENTITY, COL1 VARCHAR(100))
GO
INSERT INTO FULLDB.DBO.SQLER VALUES(REPLICATE('A', 100))
GO 10000
--단순복구모델데이터입력
CREATE TABLE SIMPLEDB.DBO.SQLER (IDX INT PRIMARY KEY IDENTITY, COL1 VARCHAR(100))
GO
INSERT INTO SIMPLEDB.DBO.SQLER VALUES(REPLICATE('A', 100))
GO 10000
--BULK LOGGED 모델데이터입력
CREATE TABLE BULKLOGGEDDB.DBO.SQLER (IDX INT PRIMARY KEY IDENTITY, COL1 VARCHAR(100))
GO
INSERT INTO BULKLOGGEDDB.DBO.SQLER VALUES(REPLICATE('A', 100))
GO 10000
DBCC SQLPERF(LOGSPACE)
GO
/*
DATABASE NAME LOG SIZE (MB) LOG SPACE USED (%) STATUS
---------------------- ------------- ------------------ -----------
FULLDB 9.992188 52.92709 0
SIMPLEDB 9.992188 52.94175 0
BULKLOGGEDDB 9.992188 52.94175 0
*/
