[MSSQL] 트랜잭션 로그 시점 복원(.trn)

파일 확장자	파일 종류	설명
.bak	백업 파일	데이터베이스 전체 또는 일부의 백업 파일입니다. 주로 복구 목적으로 사용되며, 백업 시점의 데이터와 로그 정보를 포함합니다.
.mdf	주 데이터 파일	데이터베이스의 주 파일(Main Data File)입니다. 모든 테이블, 스키마, 데이터가 저장되는 기본 데이터 파일입니다.
.ldf	로그 파일	트랜잭션 로그 파일로, 데이터베이스의 모든 트랜잭션 정보를 기록합니다. 복구 및 롤백 작업에 필요합니다.
.trn	트랜잭션 로그 백업 파일	로그 파일에서 백업된 트랜잭션 정보를 저장합니다. ldf 파일의 데이터를 주기적으로 백업할 때 생성됩니다.

 

1. 문제 상황

 

트랜잭션 로그는 데이터베이스의 모든 변경 사항(삽입, 수정, 삭제 등)을 기록하므로, 

백업된 트랜잭션 로그를 복원하면 전체 백업 이후 발생한 트랜잭션을 복구할 수 있습니다.

포인트 인 타임 복구 (Point-in-Time Recovery) 기능을 사용하면 실시간에 가까운 복구가 가능합니다.

 

2.복원 방법

==> 전체 백업을 먼저 복원한 후 트랜잭션 로그를 순차적으로 복원해야 합니다.  

복원 순서:  
1.전체 백업(Full Backup) 복원  
2.차분 백업(Differential Backup, 선택 사항) 복원 (있을 경우)  
3.트랜잭션 로그 백업(Transaction Log Backup) 복원  

 

2.1.전체 백업(Full Backup) 복원  

이때 복원 상태는 아직 완료되지 않음을 나타내는 NORECOVERY 옵션을 사용해야 합니다. 

WITH NORECOVERY 옵션을 사용하면 데이터베이스를 '복구 모드'로 유지합니다.

그 후,  WITH RECOVERY 옵션을 한번 더 사용하여 복원 중 상태를 종료하여야 합니다.

USE master
GO;

-- 데이터베이스를 SINGLE_USER 모드로 변경하여 모든 활성 연결 강제 종료
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- 데이터베이스 복원 (덮어쓰기)
RESTORE DATABASE [YourDatabase]
FROM DISK = 'D:\MSBACKUP\BaseFULLBackup.bak'
WITH NORECOVERY, REPLACE,  STATS=10 ;

 

2.2.(선택 사항) 차분 백업(Differential Backup) 복원

만약 차분 백업(전체 백업 이후에 특정 시점까지의 변경 사항만을 백업한 파일)이 존재한다면, 차분 백업을 복원합니다.   

차분 백업이 없으면 이 단계를 생략할 수 있습니다.   

RESTORE DATABASE [YourDatabase] 
FROM DISK = 'C:\backup\YourDatabase_diff.bak' 
WITH NORECOVERY;

 

2.3.트랜잭션 로그(.trn) 백업 복원  

-- 첫 번째 트랜잭션 로그(.trn) 파일 복원 (WITH NORECOVERY 옵션 사용)
RESTORE LOG YourDatabase 
FROM DISK = 'C:\Backups\YourDatabaseLogBackup1.trn'
WITH NORECOVERY;
 
-- 두 번째 트랜잭션 로그(.trn) 파일 복원
RESTORE LOG YourDatabase 
FROM DISK = 'C:\Backups\YourDatabaseLogBackup2.trn'
WITH NORECOVERY;

-- 마지막 트랜잭션 로그(.trn) 파일 복원 (WITH RECOVERY 옵션 사용)
RESTORE LOG YourDatabase 
FROM DISK = 'C:\Backups\YourDatabaseLogBackupFinal.trn'
WITH RECOVERY;

3.실습

USE [YourDatabase]
GO

-- 테이블 생성
CREATE TABLE [Table_A] (
    a int IDENTITY(1, 1) NOT NULL, 
    b varchar(6) NOT NULL
);

-- 기본 데이터 삽입 
INSERT INTO dbo.[Table_A] VALUES ('base')
SELECT * FROM  dbo.[Table_A]

-- 전체 백업 
BACKUP DATABASE [YourDatabase]
TO DISK = 'D:\MSBACKUP\BaseFULLBackup.bak' -- 14:40 

-- 백업 후  데이터 변경 
INSERT INTO dbo.[Table_A] VALUES ('add') -- 14:40
INSERT INTO dbo.[Table_A] VALUES ('add2') -- 14:41

-- 삭제
DELETE FROM [Table_A] -- 14:44
WHERE b = 'add'

-- 트랜잭션 백업 
BACKUP LOG [YourDatabase]
TO DISK = 'D:\MSBACKUP\TranBackup.trn'

 

FULL 백업 당시 데이터 , 백업 후 데이터 삽입 , 'add'  데이터 삭제

-- 트랜잭션 로그 조회
SELECT 
    [Current LSN], 
    [Transaction ID], 
    [Begin Time], 
    [Transaction Name], 
    [Transaction SID] 
FROM fn_dblog(NULL, NULL)
WHERE [Begin Time] IS NOT NULL
ORDER BY [Begin Time] DESC;

 

 

-- 다른 DB로 복원하기
RESTORE DATABASE [YourDatabase2]
FROM DISK = 'D:\MSBACKUP\BaseFULLBackup.bak'
WITH MOVE 'YourDatabase' TO 'D:\MSDATA\YourDatabase2.mdf',
MOVE 'YourDatabase_log' TO 'D:\MSLOG\YourDatabase2.ldf',
NORECOVERY, STATS=10 ;

RESTORE LOG [YourDatabase2]
FROM DISK = 'D:\MSBACKUP\TranBackup.trn'
WITH MOVE 'YourDatabase' TO 'D:\MSDATA\YourDatabase2.mdf',
MOVE 'YourDatabase_log' TO 'D:\MSLOG\YourDatabase2.ldf',
STOPAT = '2024-10-02T14:43:00' , RECOVERY, STATS=10 ;

 

복원되었다 ~

 

 

4.추가 참고 쿼리 

-- LSN으로 확인하기
SELECT * FROM fn_dblog(NULL, NULL)
WHERE Operation in ( 'LOP_DELETE_ROWS' , 'LOP_MODIFY_ROW') ;

 

-- 파일에서 LSN 확인
RESTORE HEADERONLY FROM DISK = 'D:\MSBACKUP\FullBackup_test.bak'
RESTORE HEADERONLY FROM DISK = 'D:\MSBACKUP\LogBackup_test.trn'

 

-- 에러로그로 확인하기 
EXEC sp_readerrorlog;

 

-- 실수 시간 확인  (creation_time)
SELECT 
db_name(st.dbid) DBName 
, object_schema_name(objectid, st.dbid) SchemaName 
, object_name(objectid, st.dbid) SPName 
, qs.total_elapsed_time 
, creation_time 
, last_execution_time 
, text 
FROM 
sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)st 
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
ORDER BY last_execution_time desc;

마침 😊