인터넷에 MDF 손상시 데이터 유실을 감수하고 Refair를 수행하는 방법만 존재하여 데이터 유실을 최소화 하는 방법을 단계화하여 빠른 대응을 하고자 작성하게 되었습니다
개요
-
목적
-
MDF 손상시 데이터 유실 범위 파악 및 조치 진행 단계화
-
배드섹터 발생과 같은 하드웨어 장애 등
-
-
-
참고 사이트
테스트
사전 단계 : 손상되기 전 백업 수행
BACKUP DATABASE [Test_Temp] to disk = 'C:\Test\bak\Test_Temp_F_0.bak'
backup log [Test_Temp] to disk = 'C:\Test\bak\Test_Temp_T_0.trn'-- with norecovery
backup log [Test_Temp] to disk = 'C:\Test\bak\Test_Temp_T_1.trn' --with norecovery사전 단계 :임의 페이지 정보 변경 통한 MDF 손상 유발
DBCC IND (test_Temp, 'testT1', 1)
GO
ALTER DATABASE Test_temp SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC WRITEPAGE('TestT1', 1, 552, 60, 1, 0x00, 1)
ALTER DATABASE Test_temp SET MULTI_USER확인 단계 : MDF 손상 확인
-
아래의 케이스에서는 손상된 Page에 포함되는 데이터 외 타 데이터는 정상조회 가능
-
DBCC CHECKDB를 통한 데이터 복원 조치 등 일부 과정 외 온라인 진행 가능
-
배드섹터 발생시 아래와 같은 현상이 발생
확인 단계 :CHECKDB를 통한 무결성 체크
-
실제 테스트는 Page 522가 손상되었으나, 캡쳐는 이전 테스트건임 (1:33160 → 1:522)
-
4개의 일관성 오류를 확인 / CHECKDB 결과로, 손상 수준에 맞는 복구 수준을 안내해줌 (Repair_allow_data_loss)
-
Linked List 와 같이 Pre/Nxt 포인터로 연결된 페이지 522가 손실되어 연계된 페이지들에 대한 오류 확인 가능
조치 단계 :손상된 페이지 이력 정보 확인
-
suspect_pages를 조회하여 주의 대상(에러가 발생)에 대한 이력을 보여줌.
-
Page_id 552,121 에 대한 에러 발생 확인
조치 단계 :테이블 페이지 할당 구조 확인 (유효성 없음)
-
indid 0 : Heap / 1: C-idx / 2~ : N-idx
-
손상된 데이터의 범위를 파악하기 위함이었으나 노가다성 작업이 필요하여 아래 기술하는 방식으로 접근
조치 단계 :각 페이지별 저장 데이터 및 Pre/Nxt Page 확인 : 빠른 손실 범위 파악 위함
-
조회 결과, Page 121은 정상조회 되었으며, Page 552만 조회 실패
Page 552의 Pre/Nxt Page 조회시 정상조회
DBCC PAGE(test_temp, 1, 121, 3) with tableresults,no_infomsgs;
/*m_prevPage (0:0) / m_nextPage (0:0)
num 15~84*/
DBCC PAGE(test_temp, 1, 552, 3) with tableresults,no_infomsgs;
/*m_prevPage (1:58623) / m_nextPage (1:553)
num 1028024~1028108*/
DBCC PAGE(test_temp, 1, 553, 3) with tableresults,no_infomsgs;
/*m_prevPage (1:552) / m_nextPage (1:554)
num 1028109-1028194*/조치 단계 :DBCC PAGE 조회 정보 기반 테이블 검색
-
Page 522내 저장된 Num만 오류 발생 / 그 외 정상조회
조치 단계 : 손상 페이지 복구 수행
확인 단계 : 비상로그 백업본을 통한 임시 DB [Test_Temp2] 복원 및 데이터 비교
-
Test_Temp에서 손실된 Page522 (num 1028024~1028108)가 Test_Temp2에 존재 확인
-
대상 Row Insert 해주면 됨
테스트 쿼리문
--by jyoh. 201230
--손상되기 전 백업
BACKUP DATABASE [Test_Temp] to disk = 'C:\Test\bak\Test_Temp_F_0.bak'
backup log [Test_Temp] to disk = 'C:\Test\bak\Test_Temp_T_0.trn'-- with norecovery
backup log [Test_Temp] to disk = 'C:\Test\bak\Test_Temp_T_1.trn' --with norecovery
--Page 손상
DBCC IND (test_Temp, 'testT1', 1)
GO
ALTER DATABASE Test_temp SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC WRITEPAGE('TestT1', 1, 552, 60, 1, 0x00, 1)
ALTER DATABASE Test_temp SET MULTI_USER
--손상된 테이블 조회시 에러 확인
select * from test_temp..testt1
/*
메시지 824, 수준 24, 상태 2, 줄 1
SQL Server에서 일관성 기반의 논리적인 I/O 오류가 검색되었습니다: 체크섬이 잘못되었습니다(예상: 0x1d7dba00, 실제: 0x1d7dbaed)..
파일 'C:\test\test2.mdf'의 오프셋 0x00000010310000에서 데이터베이스 ID 10에 있는 페이지 (1:552)의 읽기 중 이 오류가 발생했습니다.
자세한 내용은 SQL Server 오류 로그 또는 시스템 이벤트 로그의 추가 메시지에서 확인할 수 있습니다.
이는 데이터베이스 무결성을 위협하는 심각한 오류 상태이며 즉시 수정해야 합니다. 전체 데이터베이스 일관성 검사(DBCC CHECKDB)를 완료하십시오.
*/
--CHECKDB (Option :REPAIR_REBUILD )수행 - 복구실패
ALTER DATABASE test_temp SET SINGLE_USER WITH ROLLBACK IMMEDIATE
dbcc checkdb ('test_temp','REPAIR_REBUILD ') WITH ALL_ERRORMSGS
/*CHECKDB이(가) 테이블 'testT1'(개체 ID 245575913)에서 0개의 할당 오류와 4개의 일관성 오류를 찾았습니다.
...............
.................
repair_allow_data_loss은(는) DBCC CHECKDB(Test_Temp , repair_rebuild)이(가) 찾은 오류의 최소 복구 수준입니다
*/
-- CHECKDB (Option : repair_allow_data_loss) 수행 전 비상백업 수행 : 로그파일이 손상되지 않고 데이터파일만 손상되었을시
backup log [Test_Temp] to disk = 'C:\Test\bak\Test_Temp_T_2.trn' with CONTINUE_AFTER_ERROR
-- 테이블 IDX별 page 현황 확인 (본 작업에서 큰 의미는 없음)
select indid,dpages,reserved,used,rowmodctr from sysindexes where id =object_id('testT1')
select max(num) from testt1
select 1369780/15989 -- Row Cnt/Page Cnt = Page당 평균 Row 수 = 85
--손상된 페이지에 대한 정보 확인 (손상된 페이지 번호 : 552,121)
select db_name(database_id), * from msdb..suspect_pages
--각 페이지별 저장 데이터 및 Pre/Nxt Page 확인 : 빠른 손실 범위 파악 위함
/*조회 결과, Page 121은 정상조회 되었으며, Page 552만 조회 실패
Page 552의 Pre/Nxt Page 조회시 정상조회
*/
DBCC PAGE(test_temp, 1, 552, 3) with tableresults,no_infomsgs;
/*m_prevPage (1:58623) / m_nextPage (1:553)
num 1028024~1028108*/
DBCC PAGE(test_temp, 1, 553, 3) with tableresults,no_infomsgs;
/*m_prevPage (1:552) / m_nextPage (1:554)
num 1028109-1028194*/
DBCC PAGE(test_temp, 1, 121, 3) with tableresults,no_infomsgs;
/*m_prevPage (0:0) / m_nextPage (0:0)
num 15~84*/
--각 Page에 저장된 Num (IDX 키값) 범위 검색
select count(*) from testt1 where num not between 1028024 and 1028108 --정상조회
select count(*) from testt1 where num between 1028024 and 1028108 --에러발생
--CHECKDB 수행을 통한 손상된 페이지 재작성 : 데이터 손실 발생
ALTER DATABASE test_temp SET single_USER WITH ROLLBACK IMMEDIATE
dbcc checkdb ('test_temp','REPAIR_ALLOW_DATA_LOSS' ) WITH ALL_ERRORMSGS
/*
CHECKDB이(가) 데이터베이스 'Test_Temp'에서 0개의 할당 오류와 6개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 데이터베이스 'Test_Temp'에서 0개의 할당 오류와 6개의 일관성 오류를 수정했습니다.
*/
--임시 DB에 Test_Temp 백업본 복원
RESTORE DATABASE Test_Temp2 FROM DISK = 'C:\Test\bak\Test_Temp_F_0.bak'
WITH REPLACE, NORECOVERY,
MOVE 'test' TO 'C:\Test\test3.mdf',
MOVE 'test_log' TO 'C:\Test\test3_log.ldf';
RESTORE LOG Test_Temp2 FROM DISK = N'C:\Test\bak\Test_Temp_T_0.trn'
WITH NORECOVERY
RESTORE LOG Test_Temp2 FROM DISK = N'C:\Test\bak\Test_Temp_T_1.trn'
WITH NORECOVERY
RESTORE LOG Test_Temp2 FROM DISK = N'C:\Test\bak\Test_Temp_T_2.trn'
WITH recovery
--데이터 비교
select count(*) from Test_Temp..testt1 where num between 1028024 and 1028108 --0
select count(*) from Test_Temp2..testt1 where num between 1028024 and 1028108 --85
추가 정보
-
페이지 복원 불가 케이스
-
트랜잭션 로그
-
GAM(전역 할당 맵) 페이지
-
SGAM(공유 전역 할당 맵) 페이지
-
PFS(페이지 여유 공간) 페이지
-
모든 데이터의 부트 페이지 (파일의 0페이지)
-
1:9 페이지 (데이터베이스 부트 페이지
-
전체 텍스트 카탈로그
-
-
미러링의 경우 주 /보조간 자동 복구 조치 수행
'SQL Server > 테스트 (+스크립트)' 카테고리의 다른 글
| AlwaysOn with Basic 도입 방안 (0) | 2021.06.26 |
|---|---|
| Alwayson 구성 (0) | 2021.06.26 |
| [테스트] SQL Server TRN 백업을 통한 데이터 변경 시점 파악 (0) | 2021.02.18 |
| [테스트] SQL Server Linked Server 계정 PW 복호화 (0) | 2021.02.17 |
| [테스트]SQL Server Truncate가 되지 않은 트랜잭션 로그를 통한 삭제 데이터 확인 및 복원 (0) | 2021.02.16 |