트랜잭션 로그로 원하는 시점에 데이터 복구하기

728x90

트랜잭션(Transaction)

데이터베이스 관리 시스템(DBMS)에서 수행되는 작업의 단위를 의미합니다. 보통은 하나 이상의 데이터베이스 연산으로 구성되어 있습니다. 트랜잭션은 데이터베이스에서 일어나는 데이터의 무결성과 일관성을 보장하기 위해 중요한 개념이며, 데이터베이스에서 일어나는 각각의 작업을 안정적으로 처리하기 위해 필수적이고, 성능 및 안정성을 보장하는데 큰 역할을 합니다. 아래와 같은 속성을 가지고 있습니다.

Atomicity(원자성) : 트랜잭션은 작업의 일부분만 수행되지 않고 전체가 실행되거나 전혀 실행되지 않습니다.
Consistency(일관성) : 트랜잭션이 수행된 후에도 데이터베이스의 일관성이 유지되어야 합니다.
Isolation(고립성) : 여러 개의 트랜잭션이 동시에 실행될 경우 각각의 트랜잭션은 독립적으로 수행되어야 합니다.
Durability(지속성) : 트랜잭션이 완료된 후에는 그 결과가 영구적으로 저장되어야 합니다.

트랜잭션 로그(Transaction Log)

모든 트랜잭션과 각 트랜잭션에 의해 적용된 데이터베이스 수정 내용을 기록하는 파일입니다. DBMS는 트랜잭션 로그를 사용하여 데이터베이스의 무결성을 보장하고, 데이터 손실을 방지하며, 데이터 복구를 위한 기능을 제공합니다. 일반적으로 디스크에 저장되며, 트랜잭션의 시작 시점부터 종료 시점까지의 모든 변경 내용을 기록합니다. 트랜잭션 로그는 데이터베이스 복구를 위한 핵심 기능을 제공합니다. 데이터베이스에서 문제가 발생할 경우 트랜잭션 로그를 사용하여 문제를 해결하고 데이터를 복구할 수 있습니다. 따라서 데이터베이스 시스템에서는 트랜잭션 로그를 안정적으로 저장하고 보호하는 것이 매우 중요합니다.

SQL Server 트랜잭션 로그가 지원하는 작업으로는 개별 트랜잭션 복구, SQL Server 시작 시 모든 불안전한 트랜잭션 복구, 복원 데이터베이스, 파일, 파일 그룹 또는 페이지를 오류 지점으로 롤포워드, 트랜잭션 복제 지원, 고가용성 및 재해 복구 솔루션 지원이 있습니다.

트랜잭션 로그를 사용한 데이터 복구 작업

복구를 시작하기 전에 백업 파일과 트랜잭션 로그 파일이 모두 안전하게 보관되어 있는지 확인합니다.
데이터베이스를 복구 모드로 변경합니다.
백업 파일을 사용하여 복구를 시작합니다.
백업 파일 이후에 발생한 모든 트랜잭션 로그를 적용하여 데이터를 복구합니다.
복구 작업을 완료하고, 데이터베이스를 정상 운영 모드로 변경합니다.

복구 실습

시나리오

매일 새벽 12시 데이터베이스를 전체 백업합니다.
오후 2시까지 정상적으로 데이터를 수정합니다.
오후 2시 50분에 전체 데이터가 업데이트 되는 장애가 발생합니다.
오후 2시까지 정상적으로 사용한 데이터로 복구합니다.

실습 데이터 테이블 생성 및 데이터 삽입

CREATE TABLE [dbo].[TEST2](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[data] [varchar](50) NULL,
	[insertdate] [datetime] NULL
) ON [PRIMARY]

declare @word varchar(max) = '' 
declare @num int = 1

while (@num < 500)
begin
	
	insert into TEST2(data, insertdate) values (concat('테스트데이터', CONVERT(varchar(10), @num)) , getdate())
	
	set @num = @num + 1

end

데이터베이스 전체 백업

장애 발생 전에 데이터베이스를 먼저 전체 백업합니다. (시나리오 1 : 매일 새벽 12시 전체 백업하는 작업)

파일 이름에도 .bak 붙여서 저장합니다.

정상적인 데이터 작업

전체 백업 이후에 데이터 수정 작업을 진행합니다. (시나리오 2 : 오후 2까지 정상적으로 데이터 수정 작업)

2023-02-20 16:11:00.393

update TEST2
set data = concat('테스트데이터(수정)', CONVERT(varchar(10), @num))
  , insertdate = GETDATE()
where idx = @num

장애 발생

테이블의 모든 데이터가 일괄 변경되는 장애를 발생시킵니다.

2023-02-20 16:13:27.633

	update TEST2
	set data = '일괄변경 실수'
	, insertdate = GETDATE()

원하는 시점으로 복원

데이터베이스를 복사하여 전체 복원합니다.

복사용 DB를 생성하여 전체 백업받은 .bak 파일을 이용하여 복원합니다. 복원 전 비상 로그 백업 수행은 체크 해제해 주고, 복구 상태는 RESTORE WITH NORECOVERY로 선택합니다.

데이터베이스 test_copy 를 사용하고 있으면 복원 모드로 갈 수 없습니다.

장애가 일어난 데이터베이스에서 트랜잭션 로그 백업하기

확장자를 .trn 로 하여 트랜잭션 로그를 백업합니다.

BACKUP LOG test to disk = 'D:\Database\test_backup_2023_02_20_1530.trn'

트랜잭션 로그 백업은 전체 복구 모델이어야 백업할 수 있기 때문에 복구 모델을 확인합니다.

SELECT [name], [recovery_model_desc]
FROM sys.databases
WHERE [name] = DB_NAME()

ALTER DATABASE test SET RECOVERY full

트랜잭션 로그 파일로 원하는 시점으로 복원

중간에 트랜잭션 로그 백업을 한번 더 진행했는데 그러면 꼬일 수 있습니다. 꼬인 것을 푸는 것보다 다시 천천히 이전 단계를 반복해서 복구하는 것이 편합니다.

정상적 데이터 작업일시 ~ 장애 발생 데이터 작업일시 사이의 데이터를 STOPAY에 작성한 후에 쿼리를 실행합니다.

RESTORE LOG [test_copy3] FROM DISK = 'D:\Database\test_backup_2023_02_20_1530.trn' WITH NORECOVERY, STOPAT = '2023-02-20 15:20:00.000'

복사한 데이터베이스 상태 변경하기

RESTORE DATABASE [test_copy3] WITH RECOVERY

[복원 중] 모드가 계속 뜨는 것이 보이면, 새로고침 해주면 사라지는 것을 확인할 수 있습니다. 이후 데이터베이스를 확인해 보면 복구된 것을 확인할 수 있습니다.

참고사이트
https://ppoble.tistory.com/8#--%--%-B%EB%B-%B-%EC%-B%--%EC%-A%A-%--%EB%-D%--%EB%AF%B-DB%-D%EC%-D%--%--%--%EB%B-%B-%EC%-B%--%--%EC%A-%-----%--%--%EC%--%--%ED%--%-C%EB%A-%BC%--%EB%B-%--%EA%B-%BD
https://velog.io/@emawlrdl/SQL-Server-%EC%8B%9C%EC%A0%90%EB%B3%B5%EA%B5%AC-2-%EC%98%88%EC%A0%9C
https://zihyee.tistory.com/17
https://learn.microsoft.com/ko-kr/sql/relational-databases/backup-restore/back-up-the-transaction-log-when-the-database-is-damaged-sql-server?view=sql-server-ver16