/*
	¹®¼­¸í: 11. ´Ü¼ø º¹±¸¸ðµ¨¿¡¼­ÀÇ ÁõºÐ º¹¿ø.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) º¹±¸ ¸ðµ¨À» ´Ü¼øÀ¸·Î º¯°æ

ALTER DATABASE MyDB
	SET RECOVERY SIMPLE
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) µ¥ÀÌÅÍ Ãß°¡

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) MyDB2 À̸§À¸·Î ÁõºÐ º¹¿ø

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, RECOVERY
GO

/*
ÆÄÀÏ 1¿¡¼­ µ¥ÀÌÅͺ£À̽º 'MyDB2', ÆÄÀÏ 'MyDB_01'¿¡ ´ëÇØ 176°³ÀÇ ÆäÀÌÁö¸¦ ó¸®Çß½À´Ï´Ù .
ÆÄÀÏ 1¿¡¼­ µ¥ÀÌÅͺ£À̽º 'MyDB2', ÆÄÀÏ 'MyDB_log'¿¡ ´ëÇØ 2°³ÀÇ ÆäÀÌÁö¸¦ ó¸®Çß½À´Ï´Ù .
¸Þ½ÃÁö 3127, ¼öÁØ 16, »óÅ 1, ÁÙ 1
º¹¿øµÈ µ¥ÀÌÅͺ£À̽º 'MyDB2'ÀÌ(°¡) ´Ü¼ø º¹±¸ ¸ðµ¨À» »ç¿ëÇÏ°í ÀÖÀ¸¸ç ÇØ´ç ÆÄÀÏ 'MyDB_02g'ÀÌ(°¡) Àбâ/¾²±â¿ëÀ¸·Î Ç¥½ÃµÇ¾î ÀÖÀ¸¹Ç·Î ÀÌ ÆÄÀÏÀº Á¸ÀçÇÏÁö ¾Ê´Â »óÅ°¡ µË´Ï´Ù. µû¶ó¼­ Àбâ Àü¿ë ÆÄÀϸ¸ ÁõºÐ º¹¿øÀ¸·Î º¹±¸ÇÒ ¼ö ÀÖ½À´Ï´Ù.
¸Þ½ÃÁö 3127, ¼öÁØ 16, »óÅ 1, ÁÙ 1
º¹¿øµÈ µ¥ÀÌÅͺ£À̽º 'MyDB2'ÀÌ(°¡) ´Ü¼ø º¹±¸ ¸ðµ¨À» »ç¿ëÇÏ°í ÀÖÀ¸¸ç ÇØ´ç ÆÄÀÏ 'MyDB_03'ÀÌ(°¡) Àбâ/¾²±â¿ëÀ¸·Î Ç¥½ÃµÇ¾î ÀÖÀ¸¹Ç·Î ÀÌ ÆÄÀÏÀº Á¸ÀçÇÏÁö ¾Ê´Â »óÅ°¡ µË´Ï´Ù. µû¶ó¼­ Àбâ Àü¿ë ÆÄÀϸ¸ ÁõºÐ º¹¿øÀ¸·Î º¹±¸ÇÒ ¼ö ÀÖ½À´Ï´Ù.
RESTORE DATABASE ... FILE=<name>ÀÌ(°¡) 178°³ÀÇ ÆäÀÌÁö¸¦ 0.139ÃÊ µ¿¾È ó¸®Çß½À´Ï´Ù(10.457MB/ÃÊ).
*/

-- ´Ù½Ã º¹¿ø

RESTORE DATABASE MyDB2
	FILEGROUP = 'PRIMARY', 
	FILEGROUP = 'TRAN_GRP'
	FROM DISK = 'C:\Backup\MyDB_Full.bak'
	WITH MOVE 'MyDB_01' TO 'C:\Data\MyDB2_01.mdf',
		    MOVE 'MyDB_02' TO 'C:\Data\MyDB2_02.ndf',
		    MOVE 'MyDB_03' TO 'C:\Data\MyDB2_03.ndf',
		    MOVE 'MyDB_Log' TO 'C:\Data\MyDB2_Log.ldf',
		    PARTIAL, RECOVERY
GO


-- 8) È®ÀÎ

USE MyDB2
GO

SELECT * FROM TP
GO

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

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

SELECT * FROM TT
GO

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

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

SELECT * FROM TH
GO

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


-- 9) ÀбâÀü¿ë ÆÄÀÏ ±×·ì(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.mdf'
GO


-- 10) È®ÀÎ

USE MyDB2
GO

SELECT * FROM TH
GO

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

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


-- 11) Á¤¸®

USE Master
GO

DROP DATABASE MyDB
GO

DROP DATABASE MyDB2
GO