/*
	¹®¼­¸í: 7. ƯÁ¤ ½ÃÁ¡À¸·Î µ¥ÀÌÅͺ£À̽º º¹¿ø(2).SQL
	ÀÛ¼ºÀÚ: ÀÌÀå·¡(bestmct@sqlworld.pe.kr)
	ÀÛ¼ºÀÏ: 2008³â 6¿ù 30ÀÏ
*/


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

USE Master
GO

CREATE DATABASE MyDB
ON  PRIMARY ( 
	NAME = 'MyDB', 
	FILENAME = 'C:\Data\MyDB.mdf', 
	SIZE = 3MB , 
	FILEGROWTH = 1MB
)
LOG ON ( 
	NAME = 'MyDB_log', 
	FILENAME = 'C:\Data\MyDB_log.ldf', 
	SIZE = 1MB, 
	FILEGROWTH = 10%
)
GO


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

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


-- 3) Å×ÀÌºí ¸¸µé±â

USE MyDB
GO

CREATE TABLE MyTable (
	col1 int,
	col2 int
)
GO

INSERT INTO MyTable VALUES(1, 10)
GO


-- 4) Æ®·£Àè¼Ç ·Î±× ¹é¾÷

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


-- 5) µ¥ÀÌÅÍ Ãß°¡

INSERT INTO MyTable VALUES(2, 20)
INSERT INTO MyTable VALUES(3, 30)
INSERT INTO MyTable VALUES(4, 40)
INSERT INTO MyTable VALUES(5, 50)
GO

-- È®ÀÎ

SELECT * FROM MyTable
GO

/*
col1        col2
----------- -----------
1           10
2           20
3           30
4           40
5           50

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


-- 6) ¼öÇàµÈ DELETE¹®¿¡ ´ëÇÑ MARK ÁöÁ¤ÇÑ °æ¿ì

BEGIN TRAN Tran_Mark01
	WITH MARK 'Delete All Data'

DELETE MyTable

COMMIT
GO

-- ¼öÇà ½Ã°£ È®ÀÎ (MARK »ç¿ë ½Ã ºÒ ÇÊ¿ä)

SELECT GetDate() -- 2008-07-03 00:02:13.423
GO

-- È®ÀÎ

SELECT * FROM MyTable
GO

/*
col1        col2
----------- -----------

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


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

USE Master
GO

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


-- 8) µ¥ÀÌÅͺ£À̽º º¹¿ø(»õ·Î¿î µ¥ÀÌÅͺ£À̽º·Î º¹¿ø)

RESTORE DATABASE MyDB2
	FROM DISK = 'C:\Backup\MyDB_Full.bak'
	WITH	MOVE 'MyDB' TO 'C:\Data\MyDB2.MDF',
			MOVE 'MyDB_Log' TO 'C:\Data\MyDB2_Log.LDF', 
			NORECOVERY
GO

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

RESTORE LOG MyDB2
	FROM DISK = 'C:\Backup\MyDB_TailLog.bak'
	WITH RECOVERY, STOPBEFOREMARK = 'Tran_Mark01' -- ÇØ´ç Æ®·£Àè¼Ç ÀÌÀü±îÁö¸¸
--	WITH RECOVERY, STOPATMARK = 'Tran_Mark01' -- ÇØ´ç Æ®·£Àè¼Ç±îÁö Æ÷ÇÔÇÏ´Â °æ¿ì
GO


-- 9) È®ÀÎ

USE MyDB2
GO

SELECT * FROM MyTable
GO

/*
col1        col2
----------- -----------
1           10
2           20
3           30
4           40
5           50

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


-- 10) Áö¿öÁø Å×ÀÌºí º¹¿ø

USE MyDB
GO

-- È®ÀÎ

SELECT * FROM MyTable
GO

/*
col1        col2
----------- -----------

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

INSERT INTO MyTable
	SELECT * FROM MyDB2.dbo.MyTable
GO

-- È®ÀÎ

SELECT * FROM MyTable
GO

/*
col1        col2
----------- -----------
1           10
2           20
3           30
4           40
5           50

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


-- 11) Á¤¸®

USE Master
GO

DROP DATABASE MyDB
GO

DROP DATABASE MyDB2
GO