SQL Server 2008 page(페이지) 복원

SQL Server 2008 에서 RESTORE DATABASE page(페이지) 옵션을 사용하여 특정 페이지를 복원하는 방법에 대해서 아래와 같이 정리하였습니다.

USE SEONDUK
SELECT * FROM LT_ACT
/*
id          rname         dname        cname
-----------------------------------------
1           이요원        덕만공주       德曼公主 
2           박예진        천명공주       天明公主 
3           고현정        미실             NULL

(3 row(s) affected)
*/

-- 페이지 복원을 위한 전체 백업
BACKUP DATABASE SEONDUK TO DISK = 'D:\SQL\SEONDUK_FULL.BAK'

-- LT_ACT 테이블의 페이지 번호 확인
SELECT first FROM SYSINDEXES WHERE id = OBJECT_ID('lt_act')
SELECT CONVERT(int, 0x59)
/* 89 */

-- dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data) 명령을 사용한 페이지 손상
dbcc writepage('seonduk', 1, 89, 0, 10, 0x00000000000000000000)
/*
Msg 8939, Level 16, State 5, Line 1
Table error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 281474978611200 (type In-row data), page (1:89). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Msg 8939, Level 16, State 6, Line 1
Table error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 281474978611200 (type In-row data), page (1:89). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/


SELECT * FROM LT_ACT
/* 데이터 손상 확인
id          rname      dname      cname
----------- ---------- ---------- ----------
Msg 605, Level 21, State 3, Line 2
Attempt to fetch logical page (1:89) in database 14 failed. It belongs to allocation unit 1900544 not to 72057594039828480.
*/


DBCC CHECKTABLE(LT_ACT)
/*
Msg 8939, Level 16, State 5, Line 1
Table error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 281474978611200 (type In-row data), page (1:89). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Msg 8939, Level 16, State 6, Line 1
Table error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 281474978611200 (type In-row data), page (1:89). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'sys.sysprivs' (object ID 29).
DBCC results for 'LT_ACT'.
Msg 8928, Level 16, State 1, Line 1
Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039828480 (type In-row data): Page (1:89) could not be processed.  See other errors for details.
There are 0 rows in 0 pages for object "LT_ACT".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'LT_ACT' (object ID 2105058535).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (seonduk.dbo.LT_ACT).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/


-- 데이터 손실을 감안하고 Repair 를 수행할 필요가 없겠습니다. 페이지 복원을 시도해 봅니다.
USE MASTER
GO
BACKUP LOG SEONDUK TO DISK = 'D:\SQL\SEONDUK_LOG.BAK'

-- 페이지 복원
RESTORE DATABASE SEONDUK PAGE = '1:89' FROM DISK = 'D:\SQL\SEONDUK.BAK' WITH NORECOVERY
RESTORE LOG SEONDUK FROM DISK = 'D:\SQL\SEONDUK_LOG.BAK' WITH RECOVERY

-- 결과 확인
USE SEONDUK
SELECT * FROM LT_ACT
/*
id          rname         dname        cname
-----------------------------------------
1           이요원        덕만공주       德曼公主 
2           박예진        천명공주       天明公主 
3           고현정        미실             NULL

(3 row(s) affected)
*/

[참고자료]
데이터베이스 복원(옵션 페이지)
http://msdn.microsoft.com/ko-kr/library/ms188223.aspx

페이지 복원 수행
http://msdn.microsoft.com/ko-kr/library/ms175168.aspx

SQL Server 2005 - nieudokumentowane polecenia DBCC
http://www.microsoft.com/poland/technet/article/art0043_01.mspx


작성자 : Lai Go / 작성일자 : 2009.11.12