/*
	¹®¼­¸í: 12. Àüü º¹±¸¸ðµ¨¿¡¼­ÀÇ ÁõºÐ º¹¿ø.SQL
	ÀÛ¼ºÀÚ: ÀÌÀå·¡(bestmct@sqlworld.pe.kr)
	ÀÛ¼ºÀÏ: 2008³â 6¿ù 30ÀÏ
*/


-- 1) µ¥ÀÌÅͺ£À̽º ¸¸µé±â

USE Master
GO

CREATE DATABASE MyDB 
ON  PRIMARY 
( 
	NAME = 'MyDB_01', 
	FILENAME = 'C:\Data\MyDB_01.mdf' 
), 
FILEGROUP TRAN_GRP DEFAULT 
( 
	NAME = 'MyDB_02', 
	FILENAME = 'C:\Data\MyDB_02.ndf' 
), 
( 
	NAME = 'MyDB_03', 
	FILENAME = 'C:\Data\MyDB_03.ndf'  
),
FILEGROUP HIST_GRP ( 
	NAME = 'MyDB_04', 
	FILENAME = 'C:\Data\MyDB_04.ndf' 
) 
LOG ON ( 
	NAME = 'MyDB_log', 
	FILENAME = 'C:\Data\MyDB_log.ldf'  
)
GO


-- 2) º¹±¸ ¸ðµ¨ È®ÀÎ

SELECT name, recovery_model_desc FROM sys.databases
	WHERE name = 'MyDB'
GO


-- 3) °¢ ÆÄÀÏ ±×·ì¿¡ Å×ÀÌºí ¸¸µé±â

USE MyDB
GO

CREATE TABLE TP (
	col1 int,
	col2 int
) ON [PRIMARY]

CREATE TABLE TT (
	col1 int,
	col2 int
) ON TRAN_GRP

CREATE TABLE TH (
	col1 int,
	col2 int
) ON HIST_GRP
GO


-- 4) µ¥ÀÌÅÍ Ãß°¡ #1

INSERT INTO TP VALUES(1, 10)
INSERT INTO TT VALUES(1, 10)
INSERT INTO TH VALUES(1, 10)
GO


-- 5) HIST_GRP ÆÄÀÏ ±×·ìÀ» Àбâ Àü¿ëÀ¸·Î º¯°æ

USE Master
GO

ALTER DATABASE MyDB
	MODIFY FILEGROUP HIST_GRP READ_ONLY
GO


-- 6) µ¥ÀÌÅͺ£À̽º Àüü ¹é¾÷

BACKUP DATABASE MyDB
	TO DISK = 'C:\Backup\MyDB_Full.bak' 
	WITH INIT
GO


-- 7) µ¥ÀÌÅÍ Ãß°¡ #2

USE MyDB
GO

INSERT INTO TP VALUES(2, 20)
INSERT INTO TT VALUES(2, 20)
GO


-- 8) ·Î±× ¹é¾÷

BACKUP LOG MyDB
	TO DISK = 'C:\Backup\MyDB_Log.bak' 
	WITH INIT
GO


-- 9) MyDB2 À̸§À¸·Î ÁõºÐ º¹¿ø ½ÃÀÛ

USE Master
GO

-- a) Àüü ¹é¾÷À¸·ÎºÎÅÍ PRIMARY ÆÄÀÏ ±×·ì º¹¿ø

RESTORE DATABASE MyDB2
	FILEGROUP = 'PRIMARY'
	FROM DISK = 'C:\Backup\MyDB_Full.bak'
	WITH MOVE 'MyDB_01' TO 'C:\Data\MyDB2_01.mdf',
		    MOVE 'MyDB_Log' TO 'C:\Data\MyDB2_Log.ldf',
		    PARTIAL, NORECOVERY
GO

-- b) ·Î±× ¹é¾÷À¸·ÎºÎÅÍ º¹¿ø ¿Ï·á

RESTORE LOG MyDB2
	FROM DISK = 'C:\Backup\MyDB_Log.bak'
	WITH RECOVERY
GO

-- 10) È®ÀÎ

USE MyDB2
GO

SELECT * FROM TP
GO

/*
col1        col2
----------- -----------
1           10
2           20

(2°³ Çà Àû¿ëµÊ)
*/

SELECT * FROM TT
GO

/*
¸Þ½ÃÁö 8653, ¼öÁØ 16, »óÅ 1, ÁÙ 2
Å×À̺í 'TT'ÀÌ(°¡) ¿Â¶óÀÎ »óÅ°¡ ¾Æ´Ñ ÆÄÀÏ ±×·ì¿¡ À־ Äõ¸® ÇÁ·Î¼¼¼­¿¡¼­ ÀÌ Å×ÀÌºí ¶Ç´Â ºä¿¡ ´ëÇÑ °èȹÀ» »ý¼ºÇÒ ¼ö ¾ø½À´Ï´Ù.
*/

SELECT * FROM TH
GO

/*
¸Þ½ÃÁö 8653, ¼öÁØ 16, »óÅ 1, ÁÙ 1
Å×À̺í 'TH'ÀÌ(°¡) ¿Â¶óÀÎ »óÅ°¡ ¾Æ´Ñ ÆÄÀÏ ±×·ì¿¡ À־ Äõ¸® ÇÁ·Î¼¼¼­¿¡¼­ ÀÌ Å×ÀÌºí ¶Ç´Â ºä¿¡ ´ëÇÑ °èȹÀ» »ý¼ºÇÒ ¼ö ¾ø½À´Ï´Ù.
*/


-- 11) TRAN_GRP ÆÄÀÏ ±×·ì º¹¿ø

USE Master
GO

RESTORE DATABASE MyDB2
	FILEGROUP = 'TRAN_GRP'
	FROM DISK = 'C:\Backup\MyDB_Full.bak'
 	WITH MOVE 'MyDB_02' TO 'C:\Data\MyDB2_02.ndf',
 		    MOVE 'MyDB_03' TO 'C:\Data\MyDB2_03.ndf',
		    NORECOVERY
GO

RESTORE LOG MyDB2
	FROM DISK = 'C:\Backup\MyDB_Log.bak'
	WITH RECOVERY
GO


-- 12) È®ÀÎ

USE MyDB2
GO

SELECT * FROM TP
GO

/*
col1        col2
----------- -----------
1           10
2           20

(2°³ Çà Àû¿ëµÊ)
*/

SELECT * FROM TT
GO

/*
col1        col2
----------- -----------
1           10
2           20

(2°³ Çà Àû¿ëµÊ)
*/

SELECT * FROM TH
GO

/*
¸Þ½ÃÁö 8653, ¼öÁØ 16, »óÅ 1, ÁÙ 1
Å×À̺í 'TH'ÀÌ(°¡) ¿Â¶óÀÎ »óÅ°¡ ¾Æ´Ñ ÆÄÀÏ ±×·ì¿¡ À־ Äõ¸® ÇÁ·Î¼¼¼­¿¡¼­ ÀÌ Å×ÀÌºí ¶Ç´Â ºä¿¡ ´ëÇÑ °èȹÀ» »ý¼ºÇÒ ¼ö ¾ø½À´Ï´Ù.
*/


-- 13) HIST_GRP ÆÄÀÏ ±×·ì º¹¿ø(Àбâ Àü¿ëÀ̹ǷΠ·Î±× º¹¿ø ºÒÇÊ¿ä)

USE Master
GO

RESTORE DATABASE MyDB2
	FILEGROUP = 'HIST_GRP'
	FROM DISK = 'C:\Backup\MyDB_Full.bak'
 	WITH MOVE 'MyDB_04' TO 'C:\Data\MyDB2_04.ndf',
		    RECOVERY
GO


-- 14) È®ÀÎ

USE MyDB2
GO

SELECT * FROM TH
GO

/*
col1        col2
----------- -----------
1           10

(1°³ Çà Àû¿ëµÊ)
*/


-- 15) Á¤¸®

USE Master
GO

DROP DATABASE MyDB
GO

DROP DATABASE MyDB2
GO