[MSSQL] LSN을 활용하여 특정 시점으로 데이터 복원하기

 지난 포스팅에서 MSSQL에서의 백업의 종류에 대해서 알아봤다! MSSQL에서 백업은 Full 백업, Differential 백업, Transaction Log 백업으로 총 3가지를 지원하는 것을 알았다. 백업의 종류에 관한 내용은 아래 링크를 참고하면 된다.
https://co-no.tistory.com/5

[MSSQL] 백업 종류 - Full / Differential / Transaction

 이번 포스팅에서는 백업을 진행하여 생성된 백업 데이터(.bak)를 가지고, 실제 내가 원하는 시점으로의 복원(Restore)을 어떻게 하면 되는지 알아보자!

 

 SQL Server에서 트랜잭션의 모든 레코드는 LSN(Log Sequence Number)이라는 고유한 숫자로 식별될 수 있다. 단순히 시점으로 이해할 수도 있으며, LSN값이 클수록 시간상으로 더 이후의 시간으로 이해할 수 있다.

LSN은 SQL Server에서 'fn_dblog'라는 쿼리문과 'HEADERONLY'라는 쿼리문으로 조회를 하였을 때 서로 다른 형태로 보이게 되는데, 먼저 그 차이부터 확인해보자!

 

fn_dblog는 활성 트랜잭션 로그파일에서 정보를 확인할 수 있는 구문으로,

 

SELECT
    [Current LSN],
    [Transaction ID],
    [Transaction Name],
    Operation,
    Context,
    AllocUnitName
FROM
fn_dblog(NULL, NULL);

 

 위와 같은 쿼리 형태로 사용할 수 있다. 이때 fn_dblog의 첫번째 파라미터는 시작 LSN, 두번째 파라미터는 종료 LSN을 나타내는데, 위와 같이 둘다 NULL값을 넣으면 기본값인 트랜잭션 로그 파일의 Flush 되지 않은 모든 로그 레코드를 반환한다.

fn_dblog SELECT 결과 예시

 이제 다음 SQL 쿼리문을 통하여 'HEADERONLY' 키워드를 활용한 LSN을 확인해보자!

RESTORE HEADERONLY FROM DISK = 'C:\SQL_Backup\Adventure_Last_log.bak'

 위 쿼리에 대하여 간단히 설명하자면, C 드라이브의 'SQL_Backup' 이라는 폴더에 'Adventure_Last_log.bak' 이라는 이름의 백업파일에 대한 헤더 정보 즉, 메타 정보를 확인하겠다는 의미이다. RESTORE 키워드를 사용하였지만, HEADERONLY 키워드를 함께 쓰는 경우 실제적으로 복원(Restore)이 진행되는 것은 아니며, 단순히 백업 파일의 메타 정보를 확인하기 위한 용도이다.

 위 쿼리문에 대한 SSMS 일부 결과는 아래와 같다.

RESTORE HEADERONLY 쿼리문 결과 예시

 fn_dblog를 활용하여 조회한 LSN과 HEADERONLY를 활용하여 조회한 LSN이 다르다는 것을 확인할 수 있다. 

 

 fn_dblog 결과의 첫번째 행의 Current LSN을 보면, '00000027:00005f6f:0002' 인 것을 확인할 수 있다. 이는 [:] 부호로 구분된 3개 파트의 16진수로 된 값인데, 각각 파트별로 나누어 10진수로 변환해보면 다음과 같다. 

참고로 16진수 -> 10진수 변환은 다음 사이트를 이용하였다. https://ko.calcuworld.com/%EC%88%98%ED%95%99/16%EC%A7%84%EB%B2%95-%EA%B3%84%EC%82%B0%EA%B8%B0/

16진법 계산기 - 계산기

Part	Hex Value (16진수)	Decimal Value (10진수)
A	00000027	39
B	00005f6f	24431
C	0002	2

 "10진수로 갑자기 변환을 왜 해...?" 라는 의문이 들 것이다.

위 결과의 A파트를 그대로 사용, B파트는 10자릿수로 사용, C파트는 5자릿수로 사용하여 나타내면

'39000002443100002' 라는 값이 된다.

눈치 빠른 사람은 벌써 알겠지만, 놀랍게도 'HEADERONLY'의 LSN 형태와 비슷해 보이지 않는가?!

---

 이제 SQL Server상에서 백업을 하면 생성되는 LSN에 대하여 알아보자! 백업 파일을 활용하여 특정 시점으로 복원을 하려고 할 때 LSN을 이용하여 복원하므로 LSN에 대한 이해는 매우 중요하다고 할 수 있다.

 

백업파일의 헤더정보에는 FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN로 총 4가지의 LSN이 존재한다.

• FirstLSN : 해당 백업 파일에서 첫번째(시작) 로그 레코드의 LSN을 의미한다.
• LastLSN : 해당 백업 파일에서 마지막(끝) 로그레코드이자 다음 백업 파일의 첫번째 로그레코드의 LSN을 의미한다.
• CheckpointLSN : 주기억장치(RAM과 같은 '메모리')의 로그 레코드를 모두 보조기억장치(하드디스크)의 로그 파일에 저장하고, 버퍼(메모리)에 있는 DB상 변경된 내용을 실제 하드디스크의 DB에 반영한다. 이러한 시점을 체크포인트라 하는데, 이를 로그파일에 표시하며, 이 시점이 CheckPointLSN이 된다.
• DatabaseBackupLSN : 가장 최근의 Full 백업 세트의 CheckPointLSN을 의미한다.

 

Full(전체) 백업의 LSN

• 최초의 전체 백업인 경우, DatabaseBackupLSN 값은 0이다.
• FirstLSN = CheckpointLSN

 

Differential(차등) 백업의 LSN

• DatabaseBackupLSN = Full 백업의 CheckpointLSN
• CheckpointLSN = Differential 백업 이후 첫 번째 Transaction Log 백업의 CheckpointLSN에 매핑됨

 

Transaction Log(트랜잭션 로그) 백업의 LSN

• 모든 트랜잭션 로그 백업에 대해서 고유한 LSN으로 식별된다.

 

Full 백업 - Transaction Log 백업 간 LSN 매핑

Full백업과 Transaction Log백업의 LSN 관계

최초의 트랜잭션 로그 백업의 FirstLSN은 최초의 전체 백업을 식별한다.

또한 다음 트랜잭션 로그 백업의 FristLSN은 이전 트랜잭션 로그 백업의 LastLSN을 참고하여 기록한다.

 

 

Full 백업 - Differential 백업간 LSN 매핑

Full백업과 Differential백업의 LSN 관계

Differential 백업의 DatabaseBackupLSN은 가장 최근의 Full 백업의 CheckpointLSN과 같다.

 

 

Differential 백업 - Transaction Log 백업간 LSN 매핑

Differential백업과 Transaction Log백업의 LSN 관계

위 그림과 같이 차등 백업의 LastLSN이 트랜잭션로그 백업의 FirstLSN과 LastLSN 사이에 있을 수 있다.

 

 아래 그림은

「SSMS 실행 -> SELECT 등의 쿼리문 실행 -> 2번째 Full 백업 진행 -> DB 내용 변경 -> Differential 백업 진행 -> Transaction Log 백업 진행 -> DELETE 작업 진행 중 사고 발생 -> 비상로그 백업(Transaction Log 백업)」

이라는 시나리오를 실제 진행하였을때의 백업 세트 LSN을 확인한 결과를 도식화한 것이다.

AdventureWorks2019 테스트 백업 세트의 LSN

---

 백업세트의 LSN이 어떤식으로 되어있는지 봤으니, 이제 fn_dblog의 Current LSN을 활용하여 백업세트의 특정 시점으로 복원하는 방법에 대해 알아보자!

 

 쿼리문을 작성하기 전에 SELECT getDate() 문을 활용하여 쿼리 시작하기 전 시간을 기록해 놓았다면 복원하기가 매우 쉽겠지만, 매 쿼리마다 시간을 기록하는 것은 실제 작업 시에 매우 어려울 것이다. 

 이러한 경우, 먼저 fn_dblog를 통해 어떠한 쿼리에서 문제가 발생하였고, 그 문제를 일으킨 쿼리문의 시작시간 또는 트랜잭션의 LSN을 알아야 해당 시점으로 복구가 가능할 것이다.

'AdventureWorks2019' 라는 DB에 대한 트랜잭션 로그를 조회한다고 가정하고 아래 쿼리문을 활용해보자!

USE AdventureWorks2019
go
SELECT
    [Current LSN],
    [Operation],
    [Transaction ID],
    [Transaction Name],
    [Transaction SID],
    [Begin Time],
    [End Time],
    [Context],
    [AllocUnitName]
    [Lock Information]
FROM fn_dblog(NULL, NULL);

fn_dblog 쿼리에 대한 결과 예시

위와 같은 결과를 얻었고, 빨간색으로 표시한 DELETE 쿼리에 문제가 있다고 판단하여 해당 쿼리 이전으로 돌린다고 가정하자!

'00000027 / 00005f70 / 0001' 을 10진수로 바꾸고 LSN자릿수에 맞춰주면 '39000002443200001' 가 된다.

 데일리 백업 시스템이 존재하여 하루마다 Full 백업 및 Differential 백업, Transaction Log 백업이 모두 이루어졌다고 가정한다. 복구하려는 LSN을 확인하였다면, 비상로그 백업을 수행하여 트랜잭션 로그 백업 파일을 만들어준다.

이때 반드시 주의해야 할 점이 있는데, 기존의 Transaction Log 백업 세트에 비상로그 백업 세트를 붙이면 안된다.

즉, 기존의 Transaction Log 백업본의 파일명과 동일한 파일명으로 비상로그백업을 진행하면 안된다는 말이다. 그 이유는, 비상 로그 백업을 수행하게 되면, 기존의 백업세트가 모두 날라가고, 비상 로그 백업 세트 하나만 달랑 남게 된다. 그럼 기존의 트랜잭션 로그 백업이 되어 있던 시점의 DB로는 복구가 불가능하다.
SQL문은 아래와 같다.

BACKUP LOG AdventureWorks2019 TO DISK='C:\SQL_Backup\Adventure_LAST_log.bak'
WITH NO_TRUNCATE

-- Adventure_LAST_log.bak : 기존의 트랜잭션 로그 백업 파일이 아닌, 비상로그 백업 세트만 포함된 별도의 백업본.

 

 그리고 Full 백업 및 Differential 백업, Transaction Log 백업을 'NORECOVERY' 옵션을 넣어 RESTORE 한 후, 비상 로그 백업을 통해 RESTORE 하는 구문에서 STOPBEFOREMARK='lsn:(복구하려는 해당 LSN)' 및 'RECOVERY' 옵션을 넣어 해당 시점으로 복구한다. SQL문은 아래와 같다.

-- 마지막 RESTORE문 전에는 NORECOVERY 옵션을 넣어 백업세트들을 붙인다고 생각하면 된다.
-- AdventureWorks2019라는 원본DB에 바로 복원하는 것이 아닌 TestDB 라는 새 DB에 복원 작업을 수행한다.

-- Full 백업 세트를 복원 세트로 올리기.
RESTORE DATABASE TestDB FROM DISK='C:\SQL_Backup\AdventureWorks2019_Daily.bak'
WITH MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB.mdf',
MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf',
REPLACE, STATS=10, NORECOVERY;

-- WITH MOVE 'Logical_file_name_in_backup(백업파일에서의 논리적 DB 이름)' TO '원하는 Directory' 를 통해,
-- 원래 DB의 디렉토리와의 충돌을 피할 수 있다. (백업파일의 해당 DB를 특정 디렉토리에 복원하겠다는 의미.)

-- Differential 백업 세트를 복원 세트로 붙이기.
RESTORE DATABASE TestDB FROM DISK='C:\SQL_Backup\AdventureWorks2019_Daily_diff.bak'
WITH
MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB.mdf',
MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf',
REPLACE, STATS=10, NORECOVERY;

-- Transaction Log 백업 세트를 복원 세트로 붙이기.
RESTORE DATABASE TestDB FROM DISK='C:\SQL_Backup\Adventure_Works2019_Daily_log.bak'
WITH
MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB.mdf',
MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf',
REPLACE, STATS=10, NORECOVERY;

-- 비상 로그 백업 세트를 복원 세트로 붙여 특정 LSN으로 롤백.
-- recovery 옵션 필수.
RESTORE DATABASE TestDB FROM DISK='C:\SQL_Backup\Adventure_LAST_log.bak'
WITH
MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB.mdf',
MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf',
REPLACE, STATS=10, STOPBEFOREMARK='lsn:39000002443200001', RECOVERY;

 위 쿼리문을 수행하면, DELETE 쿼리 전으로 복구 되었다는 것을 확인할 수 있을 것이다!

위 쿼리문에서는 TestDB로 새로운 DB를 만들어 따로 복원하였으므로, 아래 쿼리와 같이 원본 DB로 옮겨주는 쿼리문을 수행해주면 DB 복원 작업이 비로소 끝난다!

INSERT INTO AdventureWorks2019.HumanResources.Department (Name, GroupName)
SELECT Name, GroupName FROM TestDB.HumanResources.Department
WHERE Name='TestDep3'
OR Name='TestDep4';

---

참고

https://sungwookkang.com/1011

백업 LSN 이해하기

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=jevida&logNo=140206499367 

트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구