이 글에서는 MSSQL Server에서 테이블의 데이터가 잘림(TRUNCATE)되거나 삭제(DELETE)된 경우, 해당 작업을 누가, 언제 수행했는지 추적하고 책임자를 파악하는 방법을 단계별로 설명합니다.
예를 들어 다음과 같은 질문에 답할 수 있습니다:
- 테이블은 언제 잘렸으며, 데이터는 언제 삭제되었는가?
- 누가 테이블을 잘랐고 데이터를 삭제했는가?
정보 수집이 필요한 이유
데이터가 의도적으로 삭제된 것인지, 아니면 실수로 인한 것인지 확인하여 담당자를 추적하고 재발 방지 대책을 마련하기 위함입니다. 실제로 이러한 정보를 찾고자 하는 고객 요청이 종종 접수됩니다.
또한 데이터 삭제 작업의 정확한 시간을 알면, 로그 백업 복원 시 STOP AT 절을 사용해 특정 시점으로 손쉽게 데이터를 복구할 수 있습니다.
문제 상황 요약
2020년 1월 5일 오후 5시부터 7시 사이에 'truncate test' 데이터베이스에서 'dump_truncate' 테이블이 잘렸고, 'dump_delete' 테이블의 데이터가 삭제되었습니다. 이 상황에서 확인해야 할 주요 질문은 다음과 같습니다:
- 무슨 일이 발생했는지 파악해야 합니다.
- 누가 'dump_delete' 테이블에서 데이터를 삭제했는가?
- 'dump_delete' 테이블에서 몇 개의 행이 삭제되었는가?
- 누가 'dump_truncate' 테이블을 잘랐는가?
- 이 테이블들은 언제 잘리고 삭제되었는가?
서버의 현재 백업 스케줄은 다음과 같습니다:
- 매주 일요일: 전체 백업(Full Backup)
- 매일 오후 1시: 차등 백업(Differential Backup)
- 15분마다: 로그 백업(Log Backup)
사전 준비 조건
- 데이터베이스가 전체 복구 모드(Full Recovery Mode)여야 합니다.
- 전체 백업, 차등 백업, 로그 백업 파일이 모두 있어야 합니다.
전체적인 접근 방식
- DELETE 및 TRUNCATE 작업이 포함된 로그 백업을 식별합니다.
- 로그 백업을 사용하여 TRUNCATE 및 DELETE 작업의 세부 정보를 확인합니다.
1. DELETE 및 TRUNCATE 작업이 포함된 로그 백업 확인
참고: 아래 작업은 원본 데이터베이스의 새 복사본을 만들어 수행했습니다.
- 1월 2일(일요일) 전체 백업을 STANDBY 모드로 복원합니다.
- 1월 5일 차등 백업을 STANDBY 모드로 복원합니다.
- 로그 백업을 STANDBY 모드로 순차적으로 복원하면서, 매번 테이블의 행 수를 확인하여 어떤 로그 백업에 TRUNCATE와 DELETE 작업 기록이 있는지 파악합니다.
오후 6시 로그 백업을 복원한 결과 'dump_truncate' 테이블이 비어 있었고, 'dump_delete' 테이블의 데이터도 사라져 있었습니다. 따라서 다음과 같이 추론할 수 있습니다:
오후 5시 45분부터 6시 사이에 'dump_truncate' 테이블이 잘렸으며, 같은 시간대에 'dump_delete' 테이블의 데이터도 삭제되었습니다.
2. 로그 백업으로 TRUNCATE 및 DELETE 작업 세부 정보 확인
1단계: 트랜잭션 ID 수집
오후 5시 45분부터 6시 사이에 발생한 모든 TRUNCATE 및 DELETE 작업의 트랜잭션 ID를 수집합니다.
조회 결과, 두 작업(DELETE와 TRUNCATE)이 오후 5시 50분에 실행되었으며, RP Dev 로그인 계정으로 수행된 것을 확인했습니다.
2단계: 트랜잭션 ID와 연관된 테이블 이름 조회
DELETE 작업 정보 조회
I) DELETE 작업의 객체 ID(Object ID)와 파티션 ID(Partition ID)를 확인합니다.
출력 결과에서 다음 정보를 도출할 수 있습니다:
- Description / Transaction Name: DELETE 작업이 수행되었음을 나타냅니다.
- Begin Time: DELETE 작업이 2022/01/05 17:50:22:493에 시작되었습니다.
- Login_Name: RP_DEV 계정이 DELETE 작업을 실행했습니다.
- Lock Information: 'HoBt' 접두사로 시작하는 각 항목은 하나의 행 삭제를 의미하며, 총 7개 행이 삭제되었습니다.
- Object ID: 데이터가 삭제된 테이블의 객체 ID입니다.
- Partition Id: 데이터가 삭제된 객체의 파티션 ID입니다.
II) 객체 ID와 파티션 ID로 해당 테이블을 찾습니다.
이제 'dump_delete' 테이블의 데이터가 RP_DEV 사용자에 의해 오후 5시 50분, 트랜잭션 ID '0000:00016a96'으로 삭제되었으며, 총 7개의 행이 삭제되었음을 확인할 수 있습니다.
TRUNCATE 작업 정보 조회
I) TRUNCATE 작업의 객체 ID와 파티션 ID를 확인합니다.
TRUNCATE 작업의 출력 결과는 DELETE 작업과 약간 다릅니다.
- Partition ID 컬럼: 올바른 파티션 ID가 직접 표시되지 않으므로, Description 컬럼에서 확인해야 합니다. 파티션 ID는 각각 72057594043564032와 72057594043629568입니다.
- Lock Description: Lock Description의 SCH_M_OBJECT 항목이 항상 올바른 Object ID를 보여줍니다. 여기서 Object ID는 885578193입니다.
II) 객체 ID와 파티션 ID로 해당 테이블을 찾습니다.
이제 'dump_truncate' 테이블이 RP_DEV 사용자에 의해 오후 5시 50분, 트랜잭션 ID '0000:00016a95'로 잘렸음을 확인할 수 있습니다.
결론
감사(Audit)가 활성화되어 있지 않은 상황에서도 누가 데이터에 대해 TRUNCATE 및 DELETE 작업을 수행했는지 파악할 수 있다면, 동일한 문제의 재발을 방지하는 데 큰 도움이 됩니다.
DELETE 작업의 경우 비즈니스 담당자가 몇 개의 행이 삭제되었는지 정확히 파악할 수 있으며, 복구 시점을 정확히 알면 데이터 복구 작업이 훨씬 수월해집니다.
이 외에도 ApexSQL Log, ApexSQL Recover와 같은 서드파티 도구를 활용하면 데이터를 복구할 수도 있습니다.
다음 세대 데이터 플랫폼 구축 여정에서 전문가의 도움을 받아보세요. 피드백 탭을 통해 의견을 남기거나 질문할 수 있으며, 언제든 저희와 대화를 시작할 수 있습니다.