--파일그룹 생성 후 백업 및 복원테스트 입니다.
--사용환경 : 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
'연구개발 > DBA' 카테고리의 다른 글
| varchar(max) + 청크 업데이트(Chunk Update) 성능 비교 (0) | 2009.07.29 |
|---|---|
| 로그 전달 환경에서 데이터 파일 추가 시 처리 문제 (0) | 2009.07.28 |
| SQL 2000 시스템 테이블 vs 2005 카테고리 뷰 (0) | 2009.07.28 |
| SQL 2005 성능 문제 해결 한글 문서 (0) | 2009.07.28 |
| Troubleshooting Performance Problems in SQL 2005 (0) | 2009.07.28 |