DBCC CHECKDB를 이용하여 손상된 데이터베이스를 복구하는 방법

데이터베이스는 여러 가지 이유로 해서 손상(Corruption)될 수 있습니다. SQL Server에서 데이터베이스가 손상되었다는 것은 단순히 디스크에서 배드 섹터(Bad Sector)가 발생하여 파일이 물리적으로 손상되는 것만을 의미하는 것은 아니며 파일(.mdf, .ndf, .ldf 등) 자체만을 놓고 봤을 때에는 이상이 없어 보여도(ChkDsk 유틸리티 실행시 오류가 발견되지 않으며 파일 복사 작업은 정상적으로 이루어지는 경우) 데이터베이스 파일 내부적으로 논리적인 오류(일반적으로 일관성 오류라고 표현함)가 발생하는 것을 포함하는 포괄적인 개념입니다.

 

일반적으로 데이터베이스가 손상되는 주된 이유는 디스크의 오류 때문이며 SQL Server에서 요청한 디스크 쓰기 작업 중 일부 또는 전부가 처리되지 않음으로 해서 발생합니다. 디스크의 오류는 디스크, 디스크 컨트롤러, 케이블 등의 하드웨어 구성요소에서 발생하는 오류뿐만 아니라 펌웨어(Firmware), 디바이스 드라이버(Device Driver) 등의 디스크에 귀속되는 소프트웨어 구성요소에서 발생하는 오류를 포함합니다. 디스크의 하드웨어 구성요소는 기본적으로 수명이 있기 때문에 오랜 시간이 경과하면 오류가 발생할 수 있으며 이런 이유로 인해서 아무런 문제없이 사용하던 디스크도 갑자기 문제가 발생할 수 있습니다. 또한, 소프트웨어 구성요소는 버그(Bug)로 인해 특정 조건에서 오동작하는 문제가 발생할 수 있으며 이런 문제는 데이터베이스 손상으로 이어질 수 있습니다.

 

데이터베이스에서 발생하는 논리적인 오류는 여러 가지 형태로 발생합니다. 8KB의 페이지로 구성되는 데이터 파일의 페이지 헤더 정보가 유효하지 않은 값을 갖는 문제, 할당 정보를 저장하고 있는 페이지의 정보와 실제 할당된 페이지가 일치하지 않는 문제, 테이블의 실제 데이터와 인덱스의 키 값이 매핑되지 않는 문제, 텍스트 칼럼에 대한 포인터 정보가 데이터를 저장하고 있는 실제 페이지와 일치하지 않는 문제, 중복 키 오류처럼 제약 조건에 위배되는 데이터가 존재하는 문제 등이 대표적인 형태입니다.

 

데이터베이스 파일에 물리적인 손상이 발생하지 않고 논리적인 오류로 인해 데이터베이스 손상이 발생했을 때 DBCC CHECKDB를 통해 복구를 시도할 수 있습니다. 하지만, DBCC CHECKDB를 통해 모든 손상을 복구할 수 있는 것은 아니며 DBCC CHECKDB를 통해 복구하는 과정에 일부 데이터가 소실되는 위험을 감수해야 할 수도 있기 때문에 DBCC CHECKDB를 사용한 복구를 권장하지는 않으며 깨끗한 백업 파일로부터 복원하는 방법을 우선적으로 고려하는 것이 좋습니다. 따라서, DBCC CHECKDB를 사용한 복구는 유효한 백업 파일이 없는 경우에 최후의 수단으로 사용하거나 데이터베이스 손상의 정도가 심하지 않아서 데이터 손실 없이도 DBCC CHECKDB를 통해 복구가 가능할 경우에만 사용하는 것이 좋습니다.

 

데이터베이스에 손상이 발생했는지 여부와 복구가 가능한 손상인지 여부는 다음과 같이 DBCC CHECKDB를 실행해 봄으로써 확인해 볼 수 있습니다. 

DBCC CHECKDB('데이터베이스명')

go

 

만약 데이터베이스의 손상이 심각할 경우에는 DBCC CHECKDB 실행이 정상적으로 완료되지 못하고 중간에 오류 메시지와 함께 중단될 수도 있는데 이럴 경우에는 DBCC CHECKDB를 통해 복구가 불가능한 경우입니다. DBCC CHECKDB를 통한 검사 작업은 많은 디스크 I/O 작업이 수반되기 때문에 기본적으로 오랜 시간이 소요되며 데이터베이스 크기가 수백GB 이상일 경우에는 디스크 성능에 따라 몇 시간 이상 소요될 수도 있습니다. DBCC CHECKDB의 검사 작업이 성공적으로 완료되면 마지막에 다음과 같이 검사 결과에 대한 요약 메시지가 표시되는데 이를 통해 데이터베이스 손상 여부를 확인하고 복구 가능 여부를 가늠해 볼 수 있습니다. 

CHECKDB이(가) 'MAIN' 데이터베이스에서 2개 할당 오류 및 239개 일관성 오류를 발견했습니다.
repair_allow_data_loss은(는) DBCC CHECKDB (MAIN)에서 발견한 오류에 대한 최소 복구 수준입니다.
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

 

상기와 같은 요약 메시지처럼 할당 오류 또는 일관성 오류가 발견되면 데이터베이스가 손상된 것이며 데이터베이스 복구 작업이 필요합니다. DBCC CHECKDB를 통해 복구가 가능할지 여부는 요약 메시지에 표시되는 최소 복구 수준을 통해 판단할 수 있습니다. 데이터베이스 손상이 심각할 경우에는 요약 메시지에 상기와 같은 최소 복구 수준이 아예 표시되지 않으며 이 때는 DBCC CHECKDB를 통해 복구가 불가능한 상태이므로 깨끗한 백업 파일로부터 복원하는 방법을 사용해야만 합니다. DBCC CHECKDB에서는 다음과 같이 3가지 복구 옵션을 지원하는데 REPAIR_FAST와 REPAIR_REBUILD는 데이터 손실 없이 복구가 가능한 옵션입니다. 따라서, 상기와 같은 요약 메시지에서 최소 복구 수준이 REPAIR_FAST나 REPAIR_REBUILD로 표시된다면 데이터 손실 없이 DBCC CHECKDB를 통해 복구가 가능한 것으로 판단할 수 있습니다.

• REPAIR_FAST : 인덱스 재생성 없이도 수정할 수 있는 간단한 오류만을 복구

• REPAIR_REBUILD : REPAIR_FAST의 작업 뿐만 아니라 인덱스 재생성 작업을 통해 복구

• REPAIR_ALLOW_DATA_LOSS : 보고된 모든 오류를 복구하되 데이터 손실 가능성이 있음

 

상기의 요약 메시지에서는 REPAIR_ALLOW_DATA_LOSS가 최소 복구 수준으로 표시되었기 때문에 DBCC CHECKDB를 통해 복구가 가능하지만 데이터 손실이 발생할 가능성이 있음을 의미합니다. 이때는 유효한 백업 파일이 존재한다면 백업으로부터 복원하는 것이 권장되는 방법이며 유효한 백업이 존재하지 않을 경우에는 DBCC CHECKDB를 통해 복구를 시도해 볼 수는 있으나 DBCC CHECKDB를 통해 모든 오류가 복구될 것으로 100% 기대할 수는 없습니다. 최소 복구 수준이 표시된다고 해서 모든 오류가 복구되는 것을 보장하는 것은 아니기 때문입니다.

 

DBCC CHECKDB를 통해 손상된 데이터베이스를 복구하기로 결정하였다면 복구 옵션을 지정하여 DBCC CHECKDB를 실행함으로써 복구를 시도할 수 있습니다. 복구 옵션을 지정하여 DBCC CHECKDB를 실행하기 위해서는 우선 데이터베이스를 단일 사용자 모드로 설정한 다음 DBCC CHECKDB를 실행할 세션에서 해당 테이터베이스를 독점적으로 사용할 수 있도록 곧바로 데이터베이스의 컨텍스트를 변경해 주어야 합니다. 이를 위해서는 다음과 같이 ALTER DATABASE 문과 USE 문을 하나의 배치로 함께 실행해 주면 되며 이렇게 함으로써 데이터베이스를 단일 사용자 모드로 변경한 직후에 다른 세션에서 데이터베이스를 사용하게 되는 문제를 예방할 수 있습니다. 

alter database 데이터베이스명 set single_user with rollback immediate
use 데이터베이스명
go

 

상기와 같이 데이터베이스를 단일 사용자 모드로 설정했다면 DBCC CHECKDB 검사 결과의 요약 메시지에 표시된 최소 복구 수준을 DBCC CHECKDB의 복구 옵션으로 지정하여 다음과 같이 실행함으로써 복구 작업을 수행할 수 있습니다. DBCC CHECKDB가 실행되면서 오류가 복구되는데 복구 옵션 없이 DBCC CHECKDB를 실행했을 때보다 오랜 시간이 소요됩니다.

DBCC CHECKDB('데이터베이스명', REPAIR_ALLOW_DATA_LOSS)
go

  

REPAIR_ALLOW_DATA_LOSS 복구 옵션을 사용하면 데이터가 손실될 가능성이 있기 때문에 DBCC CHECKDB 실행 후 데이터가 손실되었는지 여부를 확인해 보고 DBCC CHECKDB 실행 결과를 적용할지 여부를 결정하여 경우에 따라서는 이전 상태로 되돌릴 필요가 있을 수 있습니다. 이를 위해서 다음과 같이 명시적으로 사용자 트랜잭션을 열고 DBCC CHECKDB를 실행한 다음 최종 확인 후 Commit이나 Rollback해 주는 방법을 사용할 수 있습니다. 

BEGIN TRANSACTION

DBCC CHECKDB('데이터베이스명', REPAIR_ALLOW_DATA_LOSS)

-- 확인 작업 --

COMMIT (또는 ROLLBACK)

 

DBCC CHECKDB를 통한 오류 복구는 한번의 작업으로 모든 오류가 복구되지 않고 여러 차례에 걸쳐서 DBCC CHECKDB를 실행해 주어야 할 수도 있습니다. 이는 수정된 오류로 인해 새로운 오류가 발생하기도 하고 특정 오류가 먼저 수정되지 않으면 다른 오류가 수정될 수 없는 경우가 존재하기 때문입니다. 따라서, DBCC CHECKDB 실행 후 요약 메시지를 주의 깊게 살펴서 모든 오류가 수정되었는지, 일부의 오류만이 수정되었는지 여부를 확인해야 합니다. 다음은 REPAIR_ALLOW_DATA_LOSS 복구 옵션을 지정하여 DBCC CHECKDB를 실행한 후에 출력된 요약 메시지의 예로써 발견된 오류 중 일부만이 수정되었음을 알 수 있습니다. 이런 경우, 요약 메시지에 표시된 최소 복구 수준을 사용하여 다시 DBCC CHECKDB를 실행해 주어야 하며 모든 오류가 수정될 때까지 이런 과정을 반복해 주어야 합니다. 

CHECKDB이(가) 'MAIN' 데이터베이스에서 2개 할당 오류 및 239개 일관성 오류를 발견했습니다.
CHECKDB이(가) 'MAIN' 데이터베이스에서 2개의 할당 오류와 5개의 일관성 오류를 수정했습니다.
repair_allow_data_loss은(는) DBCC CHECKDB (MAIN repair_allow_data_loss)에서 발견한 오류에 대한 최소 복구 수준입니다.
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

 

DBCC CHECKDB를 통해 모든 오류가 수정되면 다음의 예와 같은 요약 메시지를 보게 되며 이를 통해 손상된 데이터베이스의 복구 작업이 성공적으로 완료되었음을 판단할 수 있습니다. 

CHECKDB이(가) 'MAIN' 데이터베이스에서 0개 할당 오류 및 239개 일관성 오류를 발견했습니다.
CHECKDB이(가) 'MAIN' 데이터베이스에서 0개의 할당 오류와 239개의 일관성 오류를 수정했습니다.
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

 

경우에 따라서는 다음의 예와 같이 DBCC CHECKDB를 반복적으로 실행해도 더 이상 오류가 수정되지 않을 수도 있는데 이 경우에는 DBCC CHECKDB를 통해 복구가 불가능한 오류이므로 깨끗한 백업에서 복원하는 등의 다른 방법을 강구해야 합니다.

CHECKDB이(가) 'MAIN' 데이터베이스에서 0개 할당 오류 및 239개 일관성 오류를 발견했습니다.
CHECKDB이(가) 'MAIN' 데이터베이스에서 0개의 할당 오류와 0개의 일관성 오류를 수정했습니다.
repair_allow_data_loss은(는) DBCC CHECKDB (MAIN repair_allow_data_loss)에서 발견한 오류에 대한 최소 복구 수준입니다.
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

 

DBCC CHECKDB를 통해 모든 오류의 복구 작업이 완료되면 다음의 명령을 실행하여 데이터베이스를 다중 사용자 모드로 변경하여 사용이 가능하도록 합니다. 

alter database 데이터베이스명 set multi_user

go 

 

데이터베이스에 일관성 오류가 발생했을 경우 오류가 발생한 부분을 실제로 사용할 때에만 오류가 발생할 뿐 다른 부분을 사용하는 데에는 문제가 없기 때문에 이런 사실을 잘 모른 채 오랫동안 방치될 수 있습니다. 이런 상황은 굉장히 위험한데 그 이유는 일관성 오류가 발생한 데이터베이스를 백업 받으면 백업 받은 파일에도 일관성 오류가 포함되기 때문입니다. 이렇게 이미 손상된 데이터베이스의 백업은 복원을 했을 때 손상된 상태가 유지되기 때문에 복구를 위해 사용될 수 없습니다. 앞에서 설명하는 과정에서 데이터베이스에 손상이 발생했을 때 가장 최선의 복구 방법은 깨끗한 백업으로부터 복원하는 것이라고 했었는데 여기서 얘기하는 깨끗한 백업은 데이터베이스 손상이 발생하기 이전에 받아둔 백업을 의미하는 것입니다. 따라서, 데이터베이스에 손상이 발생했는지 여부를 빨리 감지해 내는 것이 중요합니다.

 

데이터베이스가 손상되면 손상된 부분을 참조하는 쿼리문이 실행될 때 오류가 발생하며 오류 메시지가 SQL Server의 ErrorLog 파일에 기록됩니다. 따라서, 수시로 SQL Server의 ErrorLog를 모니터링하는 것이 중요합니다. 또한, 쿼리문에 의해서 자주 참조되지 않는 부분은 손상이 발생해도 ErrorLog에 오류 메시지가 기록되지 않을 수 있으므로 주기적으로 DBCC CHECKDB를 실행하여 데이터베이스 손상 여부를 검사해 주는 것이 좋습니다. DBCC CHECKDB 작업이 리소스(디스크 I/O, CPU, 메모리)를 다소 많이 사용하는 작업이기는 하지만 온라인성 작업(DBCC CHECKDB가 실행되는 중에도 해당 데이터베이스를 정상적으로 사용 가능함)이므로 예약 작업 등을 통해 가능한 한 자주(보통 1주일에 한번 정도 권장) 실행해 주는 것이 안전한 SQL Server 운영을 위해서 권장되는 사항입니다.