(─━┘_└━─)/ 머리만1톤(Head1Ton)

--파일그룹 생성 후 백업 및 복원테스트 입니다.

--사용환경 : Windows XP, MS-SQL 2005
--5개의 파일 그룹을 갖는 DB 생성, 백업한후 다른DB에 복사합니다.

--그 후 특정파일 그룹만 업데이트한 후 특정파일그룹만 백업 후 복원합니다.

/*

파일그룹: PRIMARY

                                    ITEM

                                    MEMBER

                                    EVENT

                                    GAMELOG

*/

 

/************************

*  1. DB생성

************************/

CREATE DATABASE TESTA

ON PRIMARY (

                  NAME = 'TESTA', FILENAME = 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTA.MDF'

                  , SIZE = 3072KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%

                  )

                  , FILEGROUP ITEM (

                  NAME = 'TESTA_FG_ITEM', FILENAME = 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTA_FG_ITEM.NDF'

                  , SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%

                  )

                  , FILEGROUP MEMBER (

                  NAME = 'TESTA_FG_MEMBER', FILENAME = 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTA_FG_MEMBER.NDF'

                  , SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%

                  )

                  , FILEGROUP EVENT (

                  NAME = 'TESTA_FG_EVENT', FILENAME = 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTA_FG_EVENT.NDF'

                  , SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%

                  )

                  , FILEGROUP GAMELOG (

                  NAME = 'TESTA_FG_GAMELOG', FILENAME = 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTA_FG_GAMELOG.NDF'

                  , SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%

                  )

LOG ON

                  (NAME = 'TESTA_LOG', FILENAME = 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTA_LOG.LDF', SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

                  , (NAME = 'TESTA_LOG2', FILENAME = 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTA_LOG2.LDF', SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

--파일그룹 ITEM 을 DEFAULT 파일그룹으로설정

ALTER DATABASE TESTA MODIFY FILEGROUP ITEM DEFAULT

GO

 

--DEFALUT 파일 그룹을 변경할 때에 주의하실 점이 있습니다.

--테이블 생성 시 파일그룹을 지정 안 할경우 DEFAULT 파일그룹에 생성이 되는데

--이 테이블들은 나중에 DEFAULT 파일그룹을 변경할 경우 변경된 파일 그룹으로 이동이 된다는 겁니다.

--테이블들이 대용량일 경우 문제가 될 수 있는 부분입니다.

 

--※아래는 SQL SERVER 온라인 설명서에 있는 부분입니다.

/*

기본파일 그룹은 ALTER DATABASE 문을 사용하여 변경할 수 있습니다.

기본파일 그룹을 바꾸면, 만들때 파일그룹을 지정하지 않은 개체가 모두 새 기본파일그룹의 데이터파일에 할당됩니다.

그러나, 시스템 개체 및 테이블은 새 기본파일 그룹이 아니라 주 파일그룹에 남게 됩니다.

*/

 

 

 

/************************

*  2. 테이블 생성 테스트

************************/

 

USE TESTA

GO

 

--CREATE TABLE ON FILEGROUP ITEM

--파일그룹을 지정 안했으나 DEFAULT 파일 그룹이 ITEM 이라서 ITEM 에 테이블이 생성 됩니다.

--인덱스는 따로 파일그룹을 지정 안할 때엔 테이블이 위치한 파일 그룹에 생성됩니다.

CREATE TABLE TD_ITEM(

                  IDX INT IDENTITY

                  , ITEM_NM VARCHAR(30)

)

GO

CREATE CLUSTERED INDEX CIDX_TD_ITEM

ON TD_ITEM(IDX)

GO

 

--SP_HELP TD_ITEM

--중략

--Data_located_on_filegroup

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

--ITEM

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

 

 

SELECT

                  B.GROUPNAME

                  , [TABLENM] = OBJECT_NAME(ID)

                  , NAME

FROM

                  SYSINDEXES A

INNER JOIN

                  SYSFILEGROUPS B

                  ON A.GROUPID = B.GROUPID

WHERE

                  A.ID = OBJECT_ID('TD_ITEM')

--RESULT

--GROUPNAME        TABLENM         NAME

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

--ITEM             TD_ITEM         CIDX_TD_ITEM

--

--(1 row(s) affected)

 

 

 

--CREATE TABLE ON FILEGROUP MEMBER

CREATE TABLE TD_MEMBER(

                  IDX INT IDENTITY

                  , MEMBER_NM VARCHAR(30)

) ON MEMBER

GO

 

CREATE CLUSTERED INDEX CIDX_TD_MEMBER

ON TD_MEMBER(IDX)

GO

 

--인덱스의 파일 그룹 확인

SELECT

                  B.GROUPNAME

                  , [TABLENM] = OBJECT_NAME(ID)

                  , NAME

FROM

                  SYSINDEXES A

INNER JOIN

                  SYSFILEGROUPS B

                  ON A.GROUPID = B.GROUPID

WHERE

                  A.ID = OBJECT_ID('TD_MEMBER')

 

--RESULT

--GROUPNAME      TABLENM        NAME

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

--MEMBER         TD_MEMBER      CIDX_TD_MEMBER

--

--(1 row(s) affected)

 

 

 

 

--CREATE TABLE ON FILEGROUP PRIMARY

--PRIMARY 파일그룹에 테이블 생성

--DEFALUT 파일그룹이 ITEM 이라서 PRIMARY 에 테이블을 생성하려면 아래와 같이 명시해줘야 됩니다.

--클러스터 인덱스의 경우 파일 그룹을 테이블이 위치한 파일 그룹과 다른곳으로 지정할 경우 테이블의 파일 그룹은 인덱스의 파일 그룹으로 변경됩니다.

--대용량의 테이블 일 경우 반드시 주의해야 할 부분입니다. 또한 테이블의 파일 그룹을 변경해야 될 경우 이 방법으로 변경 할 수도있습니다.

 

--※아래는 SQL SEVER 온라인 설명서에 있는 부분입니다.

/*

테이블에 클러스터된 인덱스가 있으면 데이터와 클러스터 된 인덱스는 항상 같은 파일 그룹에 유지됩니다.

따라서, 인덱스를 만들 다른 파일 그룹을 지정하는 기본테이블에 클러스터된 인덱스를 만들어 테이블을 한 파일그룹에서 다른 파일그룹으로 이동할 수 있습니다.

그런 다음, 인덱스를 삭제하고 기본 테이블은 새 파일 그룹에 남겨둘 수 있습니다.

*/

 

CREATE TABLE TD_PRM (

                  IDX INT IDENTITY

                  , DT DATETIME DEFAULT(GETDATE())

) ON [PRIMARY]

GO

 

--TABLE 의 파일 그룹 확인

DECLARE @ID INT

SELECT @ID = OBJECT_ID('TD_PRM')

EXEC sp_objectfilegroup @ID

--RESULT

--Data_located_on_filegroup

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

--PRIMARY

--

--(1 row(s) affected)

 

--클러스터 인덱스 생성

CREATE CLUSTERED INDEX CIDX_TD_PRM

                  ON TD_PRM(IDX) ON [PRIMARY]

GO

--다른 파일 그룹에 넌클러스터 인덱스 생성

CREATE NONCLUSTERED INDEX [NCIDX_TD_PRM]

                  ON TD_PRM(DT) ON [GAMELOG]

GO

 

--INDEX 의 파일 그룹 확인

SELECT

                  B.GROUPNAME

                  , [TABLENM] = OBJECT_NAME(ID)

                  , NAME

FROM

                  SYSINDEXES A

INNER JOIN

                  SYSFILEGROUPS B

                  ON A.GROUPID = B.GROUPID

WHERE

                  A.ID = OBJECT_ID('TD_PRM')

 

----RESULT

--GROUPNAME     TABLENM    NAME

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

--PRIMARY       TD_PRM     CIDX_TD_PRM

--GAMELOG       TD_PRM     NCIDX_TD_PRM

--

--(2 row(s) affected)

 

 

--클러스터 인덱스는 테이블과 동일한 파일그룹 PRIMARY 에 생성이 되었고

--논클러스 인덱스는 GAMELOG 파일그룹에 생성 되었습니다.

 

--클러스터 인덱스를 제거하고 테이블과 다른 파일그룹에 다시 생성하면

--테이블도 다른 파일그룹으로 이동하게 됩니다.

DROP INDEX TD_PRM.CIDX_TD_PRM

GO

--EVENT 파일 그룹에 클러스터 인덱스 생성

CREATE CLUSTERED INDEX CIDX_TD_PRM

                  ON TD_PRM(IDX) ON [EVENT]

GO

 

--SP_HELP TD_PRM

 

--RESULT

--Data_located_on_filegroup

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

--EVENT

--

--

--index_name                   index_description                                                                                                                                                                                                  index_keys

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

--CIDX_TD_PRM                  clustered located on EVENT                                                                                                                                                                                         IDX

--NCIDX_TD_PRM                 nonclustered located on GAMELOG                                                                                                                                                                                    DT

 

--테이블과 클러스터 인덱스는 EVENT 파일 그룹으로 변경되었고 넌클러스터 인덱스는 GAMELOG 파일 그룹에 그대로 있습니다.

 

 

 

 

/************************

*  3. 백업 및 복원 테스트

************************/

 

--전체 백업 후 백업본으로 데이터베이스 복사

BACKUP DATABASE TESTA

                  TO DISK = 'D:\MSSQL2005\MSSQL.1\MSSQL\BACKUP\TESTA.BAK'

                  WITH INIT

                  , SKIP

                  , STATS = 10

                 

GO

--TESTB 라는 데이터베이스에 복원

RESTORE DATABASE TESTB

                  FROM DISK = 'D:\MSSQL2005\MSSQL.1\MSSQL\BACKUP\TESTA.BAK'

                  WITH MOVE 'TESTA' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB.MDF'

                  , MOVE 'TESTA_FG_ITEM' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_ITEM.NDF'

                  , MOVE 'TESTA_FG_MEMBER' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_MEMBER.NDF'

                  , MOVE 'TESTA_FG_EVENT' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_EVENT.NDF'

                  , MOVE 'TESTA_FG_GAMELOG' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_GAMELOG.NDF'

                  , MOVE 'TESTA_LOG' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_LOG.LDF'

                  , MOVE 'TESTA_LOG2' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_LOG2.LDF'

                  , RECOVERY

                  , REPLACE

                  , STATS = 10

GO           

 

--복원확인

USE TESTB

GO

SELECT * FROM TD_ITEM

GO

 

 

 

--증분 백업 후 확인

USE TESTA

GO

--CREATE NEW TABLE

CREATE TABLE TD_ITEM2 (

                  IDX INT IDENTITY

                  , MEMBER_NM VARCHAR(30)

)

GO

 

--DIFFERENTIAL DB BACKUP

BACKUP DATABASE TESTA

                  TO DISK = 'D:\MSSQL2005\MSSQL.1\MSSQL\BACKUP\TESTA_DIFF.BAK'

                  WITH DIFFERENTIAL

                  , INIT

                  , SKIP

                  , STATS = 10

GO           

 

--기존 전체백업본과 증분 백업본을 사용하여 복원

RESTORE DATABASE TESTB

                  FROM DISK = 'D:\MSSQL2005\MSSQL.1\MSSQL\BACKUP\TESTA.BAK'

                  WITH MOVE 'TESTA' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB.MDF'

                  , MOVE 'TESTA_FG_ITEM' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_ITEM.NDF'

                  , MOVE 'TESTA_FG_MEMBER' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_MEMBER.NDF'

                  , MOVE 'TESTA_FG_EVENT' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_EVENT.NDF'

                  , MOVE 'TESTA_FG_GAMELOG' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_GAMELOG.NDF'

                  , MOVE 'TESTA_LOG' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_LOG.LDF'

                  , MOVE 'TESTA_LOG2' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_LOG2.LDF'

                  , NORECOVERY

                  , REPLACE

                  , STATS = 10

GO

RESTORE DATABASE TESTB

                  FROM DISK = 'D:\MSSQL2005\MSSQL.1\MSSQL\BACKUP\TESTA_DIFF.BAK'

                  WITH MOVE 'TESTA' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB.MDF'

                  , MOVE 'TESTA_FG_ITEM' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_ITEM.NDF'

                  , MOVE 'TESTA_FG_MEMBER' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_MEMBER.NDF'

                  , MOVE 'TESTA_FG_EVENT' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_EVENT.NDF'

                  , MOVE 'TESTA_FG_GAMELOG' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_GAMELOG.NDF'

                  , MOVE 'TESTA_LOG' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_LOG.LDF'

                  , MOVE 'TESTA_LOG2' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_LOG2.LDF'

                  , RECOVERY

                  , STATS = 10

GO

 

--복원 확인

USE TESTB

GO

--테이블 추가 확인

SELECT * FROM TESTB..TD_ITEM2

GO

 

 

--특정파일 그룹만 백업 후 복원

USE TESTA

GO

--ADD NEW TABLE

CREATE TABLE TD_ITEMADD (

                  IDX INT IDENTITY

                  , ITEMNAME VARCHAR(20)

) ON ITEM

GO

 

--파일그룹 백업시 PRIMARY 파일 그룹도 같이 백업해야 됩니다.

--여러 번 테스트를 해봤는데 PRIMARY 없이 원하는 파일그룹만 백업을 하면 백업은 됩니다만 복원이 안됩니다.

--복원된 파일 그룹이 OFFLINE 으로 되어서 사용할 수가 없습니다.

--FILE GROUP BACKUP

BACKUP DATABASE TESTA FILEGROUP = 'PRIMARY', FILEGROUP = 'ITEM'

                  TO DISK = 'D:\MSSQL2005\MSSQL.1\MSSQL\BACKUP\TESTA_ITEM.BAK'

                  WITH INIT

                  , SKIP

                  , STATS = 10

GO

RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL2005\MSSQL.1\MSSQL\BACKUP\TESTA_ITEM.BAK'

GO

--RESTORE FILELISTONLY 명령어로 백업본의 파일 목록을 보시면 IsPresent 항목에 현재 백업본에 들어 있는 파일 그룹을 확인 할 수 있습니다.

RESTORE DATABASE TESTB FILEGROUP = 'PRIMARY', FILEGROUP = 'ITEM'

                  FROM DISK = 'D:\MSSQL2005\MSSQL.1\MSSQL\BACKUP\TESTA_ITEM.BAK'

                  WITH MOVE 'TESTA' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB.MDF'

                  , MOVE 'TESTA_FG_ITEM' TO 'D:\MSSQL2005\MSSQL.1\MSSQL\DATA\TESTB_FG_ITEM.NDF'

                  , PARTIAL

                  , REPLACE

                  , RECOVERY

                  , STATS = 10

GO

--복원 확인

USE TESTB

GO

SELECT * FROM TESTB..TD_ITEMADD

GO