[MSSQL] 업데이트한 데이터 백업, 로그파일 없이 복구

실수없게 트랜잭션을 이용하자..

출처 내용을 복사하였습니다.

감사합니다.

 

어느날 잠깐 쉬러나왔다가 사무실로 돌아오니까 선배가 심각한표정으로 불렀다.

순간 '아 x됐다 뭐잘못했지?'

 

나보고 xx씨 이거 xx씨가 업데이트쳤어요?

보니까 내가한거였다.

 

실서버로 돌아가는 사이트의 단일 데이터를 수정하는 부분인데

분명히! 업데이트 하기전에 '아 나놈은 실수 많이하니까 셀렉트해서 한건 나오는지 확인하고 업데이트 하자!'

그리고 셀렉트문에서 한건 나오는거 확인하고 정작 업데이트할때 WHERE을 생략해버렸다.

업데이트하고나서 그 데이터만 확인해서 다른 데이터들도 바뀐지는 몰랐는데 며칠뒤에 상황을 파악하게 된것이다.

그 결과, 연화장에서 3만건의 유골함 위치(안치장소라고 부른다.)가 하나로 통일되버렸다.

데이터 형식도 4-405-41345 막 이런식이다 데이터를 생성하는 알고리즘도 없고 규칙도없다.

갑자기 머릿속으로 3만 가구 유가족들의 안치장소가 한곳으로 통일되고 우리회사로 전화가 물밀듯이 걸려올 것을 상상하니 바로그냥 머리가 핑 돌았다.

 

 

선배님께서 일단 빠르게 복구 방법을 찾아보고 복구 방법이 없으면 차장님께 보고를 하자고 해서 일단 내가 먼저 처리해보고싶었으나 맞는말이어서 그렇게 하기로 했다.

 

결론적으로 복구 방법이 안보였다.

 

열심히 찾아보니 대략 3가지 방법을 찾았었다.

 

1. Ms사에서 제공하는 방법

https://docs.microsoft.com/ko-kr/sql/relational-databases/backup-restore/restore-a-database-to-a-marked-transaction-sql-server-management-studio?view=sql-server-2017

분명히 저 버튼을 누르면 트랜잭션 전으로 복원이 되야하는데 당연히 내가 사고친 DB는 저 서비스가 꺼져있었다.

바닥에 주저앉고싶었지만 다음 방법을 찾았다.

 

2. 주기적으로 또는 오류가 나면 자동으로 백업해주는 백업데이터를 임포트

https://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/

여태까지 백업한 데이터를 쿼리를 이용하여 조회할수있다.

위 주소에서 쿼리문을 사용하여 조회시 위와같은 결과가 나온다.

나도 2개의 데이터가 나왔다. 근데 백업날짜가 2017년도 데이터였다.

또다시 바닥에 주저앉고 싶었지만 다른 방법을 찾아봤다.

 

2-1. 가장 최근에 실행되었던 쿼리 조회

별 의미는 없었으나 그냥 기록해뒀다.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7ef42395-0475-4820-969e-b7e7ab297974/recent-executed-queries-for-a-specific-database?forum=transactsql

 

3. 1번과 2번을 지나쳐보니 x됐다는 생각밖에 안들었다. 데이터는 수정됐는데 백업장치는 하나도 안되있고, 하지만 머리속으로 든 생각이 분명 Ms사에서 만든건데 어딘가에는 무조건 실행한 쿼리에 대해 저장되는 정보가 있을 것 같았다. 뭔가 느낌적인 느낌? 그리고 1번과 2번을 검색해보면서 가끔씩 쿼리에 대한 바이너리 데이터가 보였다. 사람이 위급해지면 초인적인 힘을 발휘한다고 쿼리 데이터와 바이너리를 집중적으로 검색해보았더니 무언가 시도해볼만한 정보가 나왔다.

굉장히 도움이 많이 되었던 사이트

http://rusanu.com/2014/03/10/how-to-read-and-interpret-the-sql-server-log/

 

sys.fn_dblog에 쿼리의 수행 결과가 바이오스 형태로 저장된다는 것을 찾아보았다.

쿼리를 실행해봤는데 기본적으로 날짜 오름차순으로 정렬되어있어서 데이터가 없는줄알았다.

선배님이 오름차순인것을 알려주셔서 내가 수행했던 쿼리부분을 찾을 수 있었다. (이부분 안찾아주셨으면 그냥 포기하려고했었다.)

 

쿼리 찾은 사이트

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/75614751-b1d0-4098-b5e5-c722439f5940/correlating-sysdmtransessiontransactions-transaction-id-to-the-fndblog?forum=sqldatabaseengine

 

아래는 사용했던 쿼리와 문제된 테이블의 sys 데이터 조회 결과다.

USE DB명 go SELECT [Current LSN], [Transaction ID], [Operation], [Transaction Name], [CONTEXT], [AllocUnitName], [Page ID], [Slot ID], [Begin Time], [End Time], [Number of Locks], [Lock Information] FROM sys.fn_dblog(NULL,NULL) WHERE Operation IN ('LOP_INSERT_ROWS','LOP_MODIFY_ROW', 'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')

굉장히 다급했기에 이해보다는 최대한 감각적으로 접근했다.

현재 나에게 필요한것은 아래와 같았다.

1. Update를 실행하였을때 기존 데이터 값(이 값이 있다면 업데이트 치려고 했던 데이터도 쌍으로 같이 있을거라고 생각했다.)

2. Update를 치려고 한 데이터의 고유 아이디 값(분명 Mssql에도 프로그램에서 사용하기 위한 고유 아이디 값이 있을 것이라고 생각했다.)

3. 확인한 데이터가 정말 내가 실행한 쿼리가 맞다는 증거

 

Current LSN은 쿼리의 고유 인덱스값인것같고

Transction ID는 트랜잭션 수행 단위별로 부여되는 ID같았다.

딱 눈에 띄는것이 대략 3만건의 트랜잭션 로그가 같은 Transction ID인것을 찾았다.

시간대도 내가 사고쳤을때와 비슷했다. Operation도 LOP_MODIFTY_ROW였다.

이거는 빼박이었다.

뒤지면서 Operation형태는 다음과 같음을 찾았다.

LOP_INSERT_ROWS - 삽입

LOP_MODIFY_ROW - 수정

LOP_DELETE_ROWS - 삭제(ㅅㄱ)

LOP_BEGIN_XACT - 트랜잭션의 시작을 알림

LOP_COMMIT_XACT - 트랜잭션의 종료를 알림 

 

나 같은경우는 LOP_BEGIN_XACT다음 3만건의 LOP_MODIFY_ROW 이후 LOP_COMMIT_XACT가 발생했다.

 

여튼 3만건의 문제되는 트랜잭션 로그를 찾았다.

 

그러면 해당 Transction ID로만 검색을 할 수 있었다.

SELECT [Current LSN], [Transaction ID], [Operation], [Transaction Name], [CONTEXT], [AllocUnitName], [Page ID], [Slot ID], [Begin Time], [End Time], [Number of Locks], [Lock Information], [Num Elements], [RowLog Contents 0], [RowLog Contents 1], [RowLog Contents 2], [RowLog Contents 3], [RowLog Contents 4], [RowLog Contents 5] FROM sys.fn_dblog(NULL,NULL) WHERE [Transaction ID]='트랜잭션 ID값'

조회결과 약 3만건의 데이터를 찾았다.

이후 데이터를 가공해서 쿼리 결과 이미지는 없고 엑셀로만 남아있다.

 

 

이 데이터가 내가 원하는 데이터임은 확인했고

확인해야하는 것은 기존 데이터와 레코드의 주소값이었다.

 

RowLog Contents 0, RowLog Contents 1의 데이터를 cast([RowLog Contents 0] as varchar(20))를 통하여 바이너리를 문자열로 바꿔서 보라는 말이 있어서 확인해보았다.

 cast([RowLog Contents 2] as varchar(20)) -> 나중에 데이터를 추출하기위해 cast로 필드를 하나씩 더만들어줬다.

급한대로 Hex코드를 문자열로 바꾸는 사이트에서 돌려보았다.

https://codebeautify.org/hex-string-converter

 

이때 딱 느낌이 왔다. 아 이거 살릴수있겠다. 자기감탄은 그만하고

약 3만건의 데이터들의 Hex값이 아래와 같았다.

0x312D3130332D3031363939	0x342D3430352D3138383838
0x312D3130422D3231383032	0x342D3430352D3138383838
0x302D3030322D3234383936	0x342D3430352D3138383838
0x312D3130342D3032333339	0x342D3430352D3138383838
0x302D3030322D3235313536	0x342D3430352D3138383838
0x312D3130342D3032333232	0x342D3430352D3138383838
0x332D3330312D3131383230	0x342D3430352D3138383838
0x302D3030422D3237313230	0x342D3430352D3138383838
0x302D3030332D3235333139	0x342D3430352D3138383838
0x302D3030372D3031383431	0x342D3430352D3138383838
0x312D3130362D3033383332	0x342D3430352D3138383838

딱봐도 왼쪽은 기존값이고 오른쪽이 내가 실수로 일괄 업데이트 친것같았다.

 

해석해보니 맞다.

 

그럼 이제 남은건 업데이트를 다시 치기위해 기존 데이터의 record location을 찾는 것이었다.

찾아보니 %%lockres%%라는 필드가 데이터의 고유 주소값을 가리킨다는 것을 찾았다.

확인해보고싶으면

select %%lockres%%, *

from 테이블

을 실행해보면 확인이 가능하다.

 

문제는 레코드의 위치데이터를 뽑아내야한다는 것이었다.

Lock Information을 확인해보면 ACQUIRE_LOCK_X RID: 6:1:19055:0가 lockers 데이와 일치한다는 것을 알 수있다. 하지만 문제가 생겼다.(문제는 ㄹㅇ 수도없이 생겼다)   갑자기 6000번대 데이터부터 ACQUIRE_LOCK_X 가 사라진것이다. 그 다음 데이터들도 뭔가를 가지고 있긴 한데 유효한 정보가 아니었다. 흠... 그래서 열심히 짱구를 굴려보니까 규칙을 찾을 수 있었다.     요 데이터의 조합이 바로 ACQUIRE_LOCK_X 값이라는 것을 알아냈다. 예를들어 0001:0000010d 1은 16진수를 10진수로 변환을 통해서 1:269:1 이라는 데이터의 조합을 가지게 된다.

그러면 다찾았다.

위치데이터, 이전 정보값

 

그리고나서 또 심각한 문제가 발생했다.

(참고로 해석1은 데이터를 복원한 결과라서 원래는 엄청 잘려서 나온다. 원본1, 원본2를 주목하자)

분명히 업데이트 칠때 기존값, 변경값 데이터가 제대로 나와야하는데

기존값은(원본1) 이상하다고 쳐도 변경값(원본2)은 일정한 값으로 전부다 업데이트 쳤기에 다르면 안되는 것이지만 꽤나 많은 데이터가 깨진다는 것을 발견했다.

 

인터넷에 깨진 데이터의 원인을 도무지 찾아봐도 나오지않아서 데이터를 보고 규칙을 찾아서 복원하기로 했다.

한번 살펴보니 규칙이 있었다.

숫자 8이 들어가면(38) 해당 값이 없어졌다.(정확히는 기존값과 변경값의 문자열이 연속으로 일치하면 사라졌다.)

그래서 만약에 원본2의 값에서 3838이 사라지면 해석1에 사라진 위치에 숫자 88을 붙여주는 방식으로 복원을 하기로 하였다.

 

 

그러면 데이터는 전부다 찾았는데 이제 어떻게 하면 3만건의 업데이트문을 만들까 생각을 해보았다.

항상 이런거 생각하는게 재밌다.

 

이번에는 엑셀을 사용해봤다.

Update문을 작성하기 위해 필요한 데이터는

원본값, 레코드 주소 두가지였다.

UPDATE SCFMC.dbo.tbl_charnel SET de_place = '3-301-11308' WHERE %%lockres%% = '1:45744:44'

 

일단 원본값을 얻기위해서는 데이터가 깨졌기때문에 꽤나 골치아펐다.

그냥 엑셀의 CONCAT과 if else if를 수없이 사용해서 깨진 데이터 사이사이에 없어진 데이터를 낑겨넣어줬다.

=IF($P13255="4-405-18888",$N13255,IF($P13255="4-405-1888",CONCATENATE($N13255,"8"),IF($P13255="4-405-188",CONCATENATE($N13255,"88"),IF($P13255="5-18888",CONCATENATE("4-40",$N13255),IF($P13255="5-1888",CONCATENATE("4-40",$N13255,"8"),IF($P13255="8888",CONCATENATE("4-405-1",$N13255),IF($P13255="4-405-18",CONCATENATE($N13255,"888"),IF($P13255="88",CONCATENATE("4-405-188",$N13255),IF($P13255="888",CONCATENATE("4-405-18",$N13255),IF($P13255="4-405-1",CONCATENATE($N13255,"8888"),IF($P13255="5-188",CONCATENATE("4-40",$N13255,"88"),IF($P13255="5-18",CONCATENATE("4-40",$N13255,"888"),IF($P13255="8",CONCATENATE("4-405-18",$N13255,"88"),"false")))))))))))))

원본데이터를 복원하기 위해 만들었다.

데이터가 깨지지 않았다면 이럴 필요는 없다.

 

위 함수를 통하여 원본 데이터를 복구하였고

 

위치 데이터는 먼저 :를 기준으로 Page ID를 스플릿해준다음 16진수를 10진수로 변환하고 Slot ID와 CONCAT함수를 이용하여 조합했다.

https://support.office.com/ko-kr/article/%ED%85%8D%EC%8A%A4%ED%8A%B8-%EB%82%98%EB%88%84%EA%B8%B0-%EB%A7%88%EB%B2%95%EC%82%AC%EB%A1%9C-%ED%85%8D%EC%8A%A4%ED%8A%B8%EB%A5%BC-%EC%97%AC%EB%9F%AC-%EC%97%B4%EB%A1%9C-%EB%82%98%EB%88%84%EA%B8%B0-30b14928-5550-41f5-97ca-7a3e9c363ed7

 

 

 

마지막으로 만들은 데이터들을 CONCAT을 통하여 UPDATE문을 생성하였다.

=CONCATENATE("UPDATE SCFMC.dbo.tbl_charnel SET de_place = '",$O13255,"' WHERE %%lockres%% = ","'",$E13255,"'")

 

 

그럼 이제 업데이트문을 돌려볼때가 됐다. (테이블을 백업하자)

위에서 추출한 위치데이터는 사건이 발생한 테이블이기 때문에 백업한 테이블에는 작업을 할 수가 없다.

업데이트를 때리고나면 전부다 반영이 되어있을것이다!!

 

진짜 삭제를 안했기에 필드가 하나라서 다행이었다.

위 작업을 하면서 실력이 많이 늘은것같다.

다음에 주변에서 비슷한 일이 발생하면 잘 도와줄 수 있을 것 같다.

감사합니다.
출처: https://ajh322.tistory.com/98 [IT 잡동사니]