DB를 날려먹어서 들어왔을테니... 우선 심심한 위로의 말씀을 전한다...!
DB를 원하는 시점으로 복구하려면, 반드시 해당 DB의 Full 백업본과 사고를 친 시점까지의 트랜잭션 로그 또는 트랜잭션 로그 백업본이 온전히 있어야 가능하다..! (Differential 백업도 진행했다면, 그 백업본도 있어야 한다.) 중간에 백업본의 시점이 빈다면... 일단 가능한 가까운 시점의 Full 백업본으로 Restore를 진행한 후, DB를 직접 하나하나 복원해야 할 것이다...
(이래서 주기적인 백업 관리는 DB 관리의 가장 기초가 되나보다.)
본 포스팅에서는 백업본이 온전히 Full+(Differential)+Transaction Log 가 존재한다고 가정하고 원하는 시점으로 복구하는 방법에 대해 알아보자!
참고로 아래에서 설명하는 DB는 Microsoft에서 제공하는 샘플 DB인 AdventureWorks2019 를 이용한다.
0. DB 실수 상황 가정
우선 샘플 DB의 원래 테이블이 어떤식으로 되어 있는지를 보자.
USE AdventureWorks2019
SELECT * FROM HumanResources.Department
그런데 당신이 WHERE 문을 깜빡하고, 부서의 모든 그룹 이름을 'IT'로 바꿔버려서 다음과 같은 결과가 된 것이다..!
-- UPDATE문이나 DELETE문을 사용할 때는 반드시 트랜잭션을 걸도록 습관화 하고..!
-- 조건문을 확실히 적는 것을 잊지 말자!
UPDATE HumanResources.Department
SET GroupName = 'IT'
-- UPDATE문 실행 후, 테이블 결과 조회.
SELECT * FROM HumanResources.Department
1. 비상로그 백업(Tail-Log Backup)
사고를 친 당신.. 침착하게 더 이상의 사고를 막아보자. 잘못 건드렸다가는 다른 사람이 진행시켜놓은 지금까지의 트랜잭션까지 다 날라갈 수도 있으니, 우선 침착하게 다음 쿼리문을 수행하여 비상로그 백업 먼저 진행해보자!
여기서 '비상로그 백업'이란, 해당 DB의 마지막 트랜잭션로그 백업 이후 시점(Last LSN)을 기준으로, 지금까지의 모든 트랜잭션 로그를 백업하는 것을 말한다. 이렇게 함으로써, 마지막 트랜잭션 로그 백업 이후의 다른 사람의 추가적인 트랜잭션까지 백업하여, 데이터 손실을 최소화 하는 것을 목적으로 한다.
주의할 점은, 일반 트랜잭션 로그 백업은 같은 파일에 계속 백업을 진행해도, 백업 세트가 붙여지는 반면, 비상 로그 백업은 기존의 백업세트를 날려버리고 해당 비상로그 백업 세트만 존재하게 한다는 것이다. 따라서 반드시 기존의 트랜잭션 로그 백업본과는 별도로 비상로그 백업본을 만들어야 한다.
-- 비상로그 백업 SQL
-- 절대 기존의 트랜잭션 로그 백업과 같은 파일명으로 백업시키지 말 것.
BACKUP LOG [당신의 DB 이름] TO DISK = '비상로그 백업을 저장시킬 디렉토리'
WITH NO_TRUNCATE, FORMAT
-- FORMAT 옵션은 만약 이전의비상로그 백업이 같은 파일명으로 존재한다면, 해당 백업 세트를 제거하기 위함. 상황에 따라서는 FORMAT 옵션을 쓰지 않아야 할 수도 있다.
-- 예시
BACKUP LOG Adventure2019 TO DISK = 'E:\SQL Server BackUp\EmergencyTrnLog.bak'
WITH NO_TRUNCATE, FORMAT2. DB 백업본 불러와서 붙이기
"붙이기(?)가 뭔 말이여...?"
이제 우리는 DB를 복구하기 위해, 기존의 DB 백업본의 Full백업본, Differential 백업본, 트랜잭션 로그 백업본, 그리고 1번에서 진행한 비상로그 백업본을 모두 불러올건데, 각각의 백업본을 합쳤을 때 비는 시점이 없도록, 우리는 이 백업본들을 '붙인다'고 표현하기로 하자!
정리하면, 기존 DB의 (Full백업본 + Differential 백업본 + 트랜잭션 로그 백업본) + 1번에서 진행한 (비상로그 백업본)
2-1. [Object Explorer] - [Databases] - [Restore Database] 클릭
2-2. 붙일 DB 백업본 Add
여기서 주의할 점은, 우리는 사고가 일어난 직후 진행한 비상로그 백업본은 이 단계에서 불러오지 않기로 하자!
2-3. 복원시킬 DB의 이름 별도 지정
현재 운영되고 있는 DB에 덮어 쓸 경우 문제가 되는 상황을 최대한 방지하기 위해, 복원시켜 놓을 DB는 웬만하면 따로 만드는 것으로 습관화하자!
2-4. Restore 옵션 지정
[Options] 항목에서, 다른 옵션들에 체크가 되어 있지는 않은지(Tail-Log 백업도 체크 해제. 우리는 이미 비상로그 백업을 진행했으므로!) 확인 후, [Recovery State] 옵션을 반드시 'NORECOVERY' 로 지정하자.
[2-1] 부터 [2-4] 까지의 과정을 모두 아래 쿼리로 한번에 진행할 수도 있다!
-- Restore 백업본의 Logical DB Name 확인이 필요하므로, 아래 쿼리로 확인
RESTORE FILELISTONLY
FROM DISK = '[백업 파일 경로]'
-- 'AdventureWorks2019_Full.bak' 이라는 백업본 파일 내의 Logical DB name 확인
RESTORE FILELISTONLY
FROM DISK = 'E:\SQL Server BackUp\AdventureWorks2019_Full.bak'-- RESTORE 쿼리 형식
RESTORE DATABASE [Restore할 DB명] FROM DISK = '[Restore할 DB에 붙일 백업 파일 경로]'
WITH MOVE '[백업 파일의 Logical mdf name]' TO '[Restore할 DB의 .mdf 파일 경로]'
, MOVE '[백업 파일의 Logical ldf name]' TO '[Restore할 DB의 .ldf 파일 경로]'
, NORECOVERY -- Restore할 백업본이 마지막 백업본이 아닐 경우 사용. 즉, 뒤에 추가로 붙일 백업본이 아직 남아 있다면, 이 옵션을 붙여야 함.
, STATS=10 -- RESTORE 진행 상황을 10% 단위로 확인 가능
-- Full 백업본 불러오기
RESTORE DATABASE AdventureWorks2019_Dummy FROM DISK = 'E:\SQL Server BackUp\AdventureWorks2019_Full.bak'
WITH MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy.mdf'
, MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy_Log.ldf'
, NORECOVERY
, STATS=10
-- Differential 백업본 붙이기
RESTORE DATABASE AdventureWorks2019_Dummy FROM DISK = 'E:\SQL Server BackUp\AdventureWorks2019_Diff.bak'
WITH MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy.mdf'
, MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy_Log.ldf'
, NORECOVERY
, STATS=10
-- 트랜잭션 로그 백업본 붙이기
RESTORE LOG AdventureWorks2019_Dummy FROM DISK = 'E:\SQL Server BackUp\AdventureWorks2019_TrnLog.bak'
WITH MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy.mdf'
, MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy_Log.ldf'
, NORECOVERY
, STATS=10
2-5. 제대로 백업본이 불러와졌는지 확인
아래 그림처럼 당신이 불러온 DB가 보이면서 'Restoring...' 상태가 되어 있다면 정상적으로 불러와진 것이다.
이때 'Restoring...' 상태는 DB가 오프라인 상태임을 의미하며, 다른 사용자는 해당 DB에 엑세스할 수 없는 상태이다.
3. 당신이 실수한 시점 찾기 (복원 시킬 시점 찾기)
아래 쿼리문을 통해 당신이 실수한 쿼리를 찾고 해당 시점을 확인하자.
SELECT TOP 1000
QS.creation_time,
--SQL
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
ST.text, -- 실행한 SQL 쿼리문
--실행 계획
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
FROM
sys.dm_exec_query_stats QS
-- sys.dm_exec_query_stats : 실행된 쿼리에 대한 통계 테이블
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
--대충 당신이 복원해야 될 시점
WHERE creation_time BETWEEN '2022-06-29 11:00:00' AND '2022-06-29 11:20:00'
ORDER BY
QS.creation_time DESC
위 쿼리의 결과를 보면, 2번 행에서 우리가 잘못 수행한 쿼리임을 알 수 있다. 그렇다면 이 이전 시점으로 돌려야 하므로, '2022-06-29 11:19:35:947' 이라는 시점을 메모해두고 있자!
4. Restore 진행 및 Recovery State를 'RECOVERY'로 바꾸기
특정 시점 이전으로 복원을 할 때에는 아래 쿼리를 수행하면 된다.
복원이 완료되면 반드시 DB를 'Restroing...' 상태에서 해제시켜, 복원 과정을 '끝맺음' 해야 한다.
-- 비상로그 백업본에서 해당 시점을 찾아 복원한다.
RESTORE LOG AdventureWorks2019_Dummy FROM DISK = 'E:\SQL Server BackUp\EmergencyTrnBak.bak'
WITH MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy.mdf',
MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Dummy_Log.ldf',
NORECOVERY, STOPAT = '2022-06-29 11:19:35.946', STATS=10 -- 실수한 'UPDATE 트랜잭션'의 시작 시간 -0.001초 지점으로 롤백
-- 복원이 끝난 후 DB의 'Restoring...' 상태를 해제.
RESTORE DATABASE AdventureWorks2019_Dummy WITH RECOVERY여기까지 하면, 데이터는 원래 상태로 되돌렸다고 볼 수 있다!
본 포스팅에서는 Dummy DB를 통해 복원하였으므로, 이 복원된 DB에서의 값으로 원래 DB도 바꿔주어야 하는 과정이
마지막으로 하나 남아있다!
5. 복원한 Dummy DB를 통해 원래 DB의 바뀐 부분 원복하기
UPDATE Ori
SET GroupName = Dummy.GroupName -- 본 예시에서는 'GroupName' 컬럼의 값들만 바뀌었기 때문에, 이 컬럼만 복원된 값으로 update 한다.
FROM AdventureWorks2019.HumanResources.Department AS Ori
INNER JOIN AdventureWorks2019_Dummy.HumanResources.Department AS Dummy
ON Ori.DepartmentID = Dummy.DepartmentID -- Primary Key인 'DepartmentID'를 기준으로 Inner Join
WHERE Ori.GroupName='IT' -- 실수로 업데이트시킨 값이 'IT' 이므로.실수한 방식마다 원복하는 쿼리도 모두 다르겠지만, 본 예시에서는 WHERE절을 빼먹은 UPDATE 문의 복원이므로, 간단하게 JOIN 문을 활용한 UPDATE 를 통해 데이터를 원복하도록 한다.
위 쿼리보다 더 효율적인 쿼리가 있다면, 댓글로 남겨주시길 바랍니다! (쿼리 효율성에 대해서는 아직 공부가 많이 필요하다.)
참고
[MSSQL][시점복구] DB 원하는 시점으로 복구하기(+복구시점찾기, + 복구진행현황보기)
MSSQL DB 원하는 시점으로 복구하기 😱 이 글을 읽으러 온 너... DB 날려먹었구나!😱 내가 DB 작업할 때는 꼭 트랜젝션 걸고하라했잖아!! 못들었다고? 그럼 다시 한번 보고와 https://ppoble.tistory.com/entr
ppoble.tistory.com
최근댓글