[MSSQL] 트랜잭션 로그 백업(Transaction Log Backup)

[MSSQL] 트랜잭션 로그 백업(Transaction Log Backup)

로그 백업을 만들려면 최소한 하나의 전체 백업을 만들어야 합니다. 그렇게 해야 로그가 백업 중일 때를 제외하고 언제든지 트랜잭션 로그를 백업할 수 있습니다.

로그 백업을 자주 수행하여 작업 손실 가능성을 최소화하고 트랜잭션 로그를 잘라내는 것이 좋습니다.

데이터베이스 관리자는 보통 주기적(예: 매주)으로 전체 데이터베이스 백업을 만들고, 상황에 따라 더 짧은 간격(예: 매일)으로 차등 데이터베이스 백업을 만듭니다. 또한 데이터베이스 백업과 상관없이 더 자주(예: 매 10분) 트랜잭션 로그를 백업합니다. 지정된 백업 유형의 최적 간격은 데이터의 중요도, 데이터베이스의 크기 및 서버의 작업과 같은 요소에 따라 달라집니다.

로그 백업 시퀀스의 작동 방법

트랜잭션 로그 백업 로그 체인 시퀀스는 데이터 백업과 독립되어 있습니다. 예를 들어 이벤트가 다음과 같은 순서로 발생한다고 가정합니다.

Time	이벤트
8:00 A.M.	데이터베이스를 백업합니다.
정오	트랜잭션 로그를 백업합니다.
오후 4:00	트랜잭션 로그를 백업합니다.
오후 6:00	데이터베이스를 백업합니다.
8:00 P.M.	트랜잭션 로그를 백업합니다.

저녁 8시에 만든 트랜잭션 로그 백업에는 오후 4시부터 저녁 8시까지 기록한 트랜잭션 로그 레코드가 포함됩니다. 이 시간대에는 전체 데이터베이스 백업을 만든 오후 6시도 포함됩니다. 트랜잭션 로그 백업의 시퀀스는 오전 8시에 만든 첫째 전체 데이터베이스 백업부터 저녁 8시에 만든 마지막 트랜잭션 로그 백업까지 이어집니다. 

권장사항

트랜잭션 로그가 손상되면 최근의 유효 백업 이후에 수행한 작업이 손실됩니다. 따라서 내결함성이 있는 저장소에 로그 파일을 보관하는 것이 좋습니다.

데이터베이스가 손상되었거나 데이터베이스를 복원하려는 경우에는 데이터베이스를 현재의 지정 시간으로 복원할 수 있도록 비상 로그 백업을 만드는 것이 좋습니다.

기본적으로 백업 작업을 성공적으로 수행할 때마다 SQL Server 오류 로그와 시스템 이벤트 로그에 항목이 추가됩니다. 로그를 자주 백업하는 경우 이러한 성공 메시지는 바로 누적되므로 엄청난 오류 로그가 쌓여 다른 메시지를 찾기 힘들 수 있습니다. 이 경우 스크립트가 이러한 로그 항목에 종속되지 않을 경우 추적 플래그 3226을 사용하여 이러한 항목을 표시하지 않을 수 있습니다. 

트랜잭션 로그 백업을 적용하려면 다음 요구 사항을 충족해야 합니다.

• 복원 순서를 위한 충분한 로그 백업: 복원 순서를 완료하려면 백업된 로그 레코드가 충분히 있어야 합니다. 복원 시퀀스를 시작하려면 필요한 로그 백업을 반드시 확보해야 합니다.

• 올바른 복원 순서: 바로 이전의 전체 데이터베이스 백업 또는 차등 데이터베이스 백업을 먼저 복원해야 합니다. 그런 후 전체 또는 차등 데이터베이스 백업 후에 생성된 모든 트랜잭션 로그를 시간순으로 복원해야 합니다. 이 로그 체인의 트랜잭션 로그 백업이 손실되거나 손상된 경우 손실된 트랜잭션 로그 이전의 트랜잭션 로그만 복원할 수 있습니다.

• 데이터베이스가 아직 복구되지 않음: 마지막 트랜잭션 로그가 적용될 때까지 데이터베이스를 복구할 수 없습니다. 로그 체인이 끝나기 이전의 중간 트랜잭션 로그 백업 중 하나를 복원한 후 데이터베이스를 복구할 경우 해당 시점 이후의 데이터베이스를 복원하려면 전체 데이터베이스 백업부터 시작하여 전체 복원 시퀀스를 다시 시작해야 합니다.

  팁 최선의 방법은 모든 로그 백업을 복원하는 것입니다(RESTORE LOG database_name WITH NORECOVERY). 그런 후 마지막 로그 백업을 복원한 후 데이터베이스를 별도의 작업으로 복구합니다(RESTORE DATABASE database_name WITH RECOVERY).

복구 및 트랜잭션 로그

복원 작업을 완료하고 데이터베이스를 복구할 때 완료되지 않은 트랜잭션은 모두 롤백되는데 이것을 실행 취소 단계라고 합니다. 롤백은 데이터베이스의 무결성을 복원하는 데 필요합니다. 롤백 후 데이터베이스는 온라인 상태가 되고 트랜잭션 로그 백업이 더 이상 데이터베이스에 적용되지 않습니다.

예를 들어 일련의 트랜잭션 로그 백업은 장기 실행 트랜잭션을 포함합니다. 트랜잭션의 시작은 첫 번째 트랜잭션 로그 백업에 기록되지만 트랜잭션의 끝은 두 번째 트랜잭션 로그 백업에 기록됩니다. 첫 번째 트랜잭션 로그 백업에는 커밋 또는 롤백 작업의 기록이 없습니다. 첫 번째 트랜잭션 로그 백업이 적용될 때 복구 작업이 실행되면 장기 실행 트랜잭션은 완료되지 않은 것으로 취급되고 해당 트랜잭션에 대한 첫 번째 트랜잭션 로그 백업에 기록된 데이터 수정이 롤백됩니다. SQL Server 에서는 이 시점 이후에 두 번째 트랜잭션 로그 백업을 적용할 수 없습니다.

참고: 경우에 따라서는 로그 복원 중에 파일을 명시적으로 추가할 수 있습니다.

로그 백업을 사용하여 오류 지점까지 복원

예를 들어 다음과 같은 순서의 이벤트가 발생한다고 가정합니다.

Time	이벤트
8:00 A.M.	데이터베이스를 백업하여 전체 데이터베이스 백업을 만듭니다.
정오	트랜잭션 로그를 백업합니다.
오후 4:00	트랜잭션 로그를 백업합니다.
오후 6:00	데이터베이스를 백업하여 전체 데이터베이스 백업을 만듭니다.
8:00 P.M.	트랜잭션 로그를 백업합니다.
9:45 P.M.	오류가 발생합니다.

데이터베이스를 오류 발생 시점인 오후 9:45의 상태로 복원하려면 다음 대체 절차 중 하나를 사용하십시오.

대체 1: 가장 최근의 전체 데이터베이스 백업을 사용하여 데이터베이스 복원

1. 오류 발생 시점에 활성화되어 있던 트랜잭션 로그의 비상 로그 백업을 만듭니다.

2. 8:00 A.M.의 전체 데이터베이스 백업을 시간이 오래 걸립니다. 대신 가장 최근의 6:00 P.M. 전체 데이터베이스 백업을 복원한 다음 8:00 P.M. 로그 백업 및 비상 로그 백업을 적용합니다.

대체 2: 이전의 전체 데이터베이스 백업을 사용하여 데이터베이스 복원

참고: 이 대체 프로세스는 문제로 인해 6:00 P.M.의 전체 데이터베이스 백업을 사용할 수 없는 경우에 유용합니다. 시간이 오래 걸립니다. 6:00 P.M.의 전체 데이터베이스 백업에서 복원하는 것보다 시간이 오래 걸립니다.

1. 오류 발생 시점에 활성화되어 있던 트랜잭션 로그의 비상 로그 백업을 만듭니다.

2. 8:00 A.M.의 전체 데이터베이스 백업을 복원한 다음 4개의 트랜잭션 로그 백업을 순서대로 복원합니다. 이렇게 하면 완료된 모든 트랜잭션을 9:45 P.M.까지 롤포워드합니다.

   이 대체 프로세스를 통해 일련의 전체 데이터베이스 백업에서 트랜잭션 로그 백업 체인을 유지함으로써 제공되는 중복 보안을 확인할 수 있습니다.

참고: 경우에 따라 트랜잭션 로그를 사용하여 데이터베이스를 지정 시간으로 복원할 수도 있습니다. 자세한 내용은 SQL Server 데이터베이스를 지정 시간으로 복원(전체 복구 모델)을 참조하세요.

Transaction Log Backup (로그 백업)

트랜잭션 로그 백업에 대해 이해하려면 먼저 트랜잭션 로그란 놈에 대해서 이해를 해야겠죠?

트랜잭션 로그란 SQL Server에서 실행되는 모든 SQL문을 기록한 로그입니다. 어느 세션이 어떤 SQL을 실행했는지가 순차적으로 기록됩니다. 우리가 트랜잭션을 Rollback 시켰을 때 Begin Tran 시점 이전으로 되돌릴 수 있는 것은 트랜잭션을 시작한 이후의 모든 작업내용이 트랜잭션 로그에 기록되어 있기 때문입니다.

트랜잭션 로그는 다음과 같이 하여 백업받을 수 있습니다. (물론 GUI로도 가능합니다.)

view plaincopy to clipboardprint?

1. BACKUP LOG [DATABASENAME] TO [BACKUPDEVICENAME]  

 

로그 백업은 위의 데이터 백업과는 다른 몇가지 특성이 있습니다.

① 일단 백업된 트랜잭션 로그는 자동으로 삭제됩니다. 이때 ldf파일 사이즈가 줄어들지는 않지만 ldf 내에서 로그가 삭제되기 때문에 해당 ldf 파일에는 새로운 로그를 기록할 수 있는 빈 공간이 확보됩니다. 따라서, 주기적으로 트랜잭션 로그를 백업받으면, 별도로 로그를 삭제하지 않더라도 ldf 파일이 계속 커지는 것을 방지할 수 있습니다.

② 트랜잭션 로그는 가지고 있는 최신 풀백업 혹은 풀백업 + DifferentialBackup 세트로 부터 복원을 원하는 시점까지의 모든 백업본이 존재하지 않으면 아무 의미가 없습니다. 백업받은 로그 중 한 세트라도 분실하면 소용 없다는 의미입니다. 물론 풀백업을 한번도 받은적이 없다면 트랜잭션 로그는 소용없습니다.

③ 트랜잭션 로그가 온전하다면 Fault가 발생한 Database를 Fault 발생 직전의 시점으로 복원할 수 있습니다. 또한, 특정 시점으로의 Rollback 등 미세한 복원이 가능합니다.

  ※ 파일 백업은... 논외로 치겠습니다. ^^;

2. 트랜잭션 로그를 이용한 데이터 복원

트랜잭션 로그를 구체적으로 어떤 식으로 써먹을 수 있는지 두가지 예를 들어 살펴보겠습니다.

  1) 장애 시점까지의 DB 복구

다음과 같이 백업을 받고 있습니다.

① 10월 1일 DB Full Backup

② 10월 5일 Differential Backup

③ 10월 7일 Transaction Log Backup

④ 10월 9일 Differential Backup
⑤ 10월 10일 Transaction Log Backup

⑥ 10월 11일 Transaction Log Backup

만약 10월 12일에 DB에 Fault가 발생했다고 가정하겠습니다.

Q : DB를 Fault 발생 시점으로 완전하게 복구할 수 있을까요?
A : Yes~! .ldf파일의 트랜잭션 로그만 손상되지 않았다면 가능합니다.

복구 절차는 다음과 같습니다.

현재 활성화된 트랜잭션 로그를 백업

가장 최근에 수행한 전체 데이터베이스 백업을 복원

차등 백업이 있으면 가장 최근의 것을 복원

가장 최근에 차등 또는 전체 데이터베이스를 백업한 후에 만든 트랜잭션 로그 백업을 모두 차례대로 적용

가장 최근에 로그를 백업한 후에 변경된 사항을 모두 수동으로 다시 실행

위의 케이스에서는 다음과 같이 되겠지요.

① Fault가 발생한 시점의 트랜잭션 로그를 백업

② 10월 1일자 DB Full Backup을 Restore

③ 10월 9일자 Differential Backup을 Restore

④ 10월 10일자 트랜잭션 로그를 Restore

⑤ 10월 11일자 트랜잭션 로그를 Restore
⑥ ①에서 백업받은 트랜잭션 로그를 Restore

Fault 발생지점까지 복원시키는 자세한 방법은 여기를 참고하세요.

  2) 특정 시점까지의 장애복구 (STOPAT)

10월 12일에 실수로 중요 테이블을 Delete 시켰다고 가정합니다. 혹은 Update 하다가 실수로

Where 절을 깜빡잊고 안적었다던지.. 하여간... 이런 상황. 백업받은 상황은 위와 동일하다고 가정합니다. 

이러한 경우에도 복원이 가능할까요?

넵! 트랜잭션 로그를 복원할 때는 "STOPAT"이라는 옵션을 통해 특정 시점까지만 복원이 가능합니다.

① 문제가 발견된 시간을 기록하고, 해당 DB를 중지시킨 후 즉시 트랜잭션 로그를 백업받음

② 10월 1일자 DB Full Backup을 Restore

③ 10월 9일자 Differential Backup을 Restore

④ 10월 10일자 트랜잭션 로그를 Restore

⑤ 10월 11일자 트랜잭션 로그를 Restore

⑥ ①에서 백업받은 트랜잭션 로그를 "STOPAT" 옵션을 주어 ①에서 기록한 시간 직전까지만 Restore함.

⑤에 해당하는 작업은 다음과 같이 하면 됩니다.

view plaincopy to clipboardprint?

1. RESTORE LOG [DATABASENAME]  
2. FROM  [BACKUPDEVICENAME]  
3. WITH FILE = 3, STOPAT = '2009-10-15 14:15:23' , RECOVERY  

지정 시간까지 복원시키는 방법에 대한 자세한 내용은 여기를 참고하세요.

위의 SQL 문 중에서 FILE=3 이라는 옵션은 "LogBackupDeviceName"이라는 백업 디바이스의 백업세트 중 세번째 파일을 이용해 백업하라는 의미입니다. 백업 세트 내에 들어있는 파일 목록을 확인하는 방법은 다음과 같습니다.

view plaincopy to clipboardprint?

1. RESTORE FILELISTONLY FROM [BACKUPDEVICENAME];  

RESTORE 문의 옵션에 대한 자세한 내용은 여기를 참고하세요. 

3. 트랜잭션 로그를 삭제하여 로그파일(*.ldf 파일) 사이즈 줄이기

 위에서 얘기한 바와 같이 트랜잭션 로그를 백업받으면 자동으로 트랜잭션 로그가 삭제됩니다. 하지만 평소에 트랜잭션 로그를 백업받지 않던 DB서버에서 HDD 가 가득 찼다던지... 하면 트랜잭션 로그를 백업받을 여유가 없을 뿐더러... 무엇보다도 트랜잭션 로그를 삭제한다고 해서 무조건 .ldf 파일 사이즈가 줄어들지는 않는다는 것이 문제입니다. ^^

 트랜잭션 로그를 백업받지 않고 파일 사이즈를 줄이는 방법은 다음과 같이 두 단계로 이루어집니다.

1) 로그 삭제 (BACKUP LOG ~)

 .ldf 파일 내에서 로그를 삭제합니다. 

 위에서 얘기한 바와 같이 "BACKUP LOG" 문을 실행하면 트랜잭션 로그를 백업한 후 자동으로 로그를 삭제하지만, 다음과 같은 옵션을 주면 백업받지 않고 로그만 삭제할 수 있습니다.

view plaincopy to clipboardprint?

1. DBCC SQLPERF(LOGSPACE) -- 현재 로그파일의 상태를 확인한다.  
2. BACKUP LOG [DATABASENAME] WITH NO_LOG  -- 실제로 로그를 삭제하는 명령  
3. --BACKUP LOG [DATABASENAME] WITH TRUNCATE_ONLY 위의 SQL과 동일한 효과  

2) 로그파일 축소 (DBCC SHRINK~~)

 .ldf 파일의 사이즈를 축소합니다.

 위의 로그 삭제 작업은 ldf 파일 내에서 로그를 삭제하여 공간을 확보하지만 정작 ldf 파일의 사이즈는 줄어들지 않기 때문에 HDD의 여유공간을 확보하지 않습니다. 로그를 삭제한 후 아래와 같이 로그 파일에 대해 SHRINK 작업을 수행하면 위에서 삭제한 트랜잭션 로그의 공간만큼 실제로 ldf 파일을 축소시켜 HDD의 공간이 확보됩니다.

view plaincopy to clipboardprint?

1. SP_HELPDB [DATABASENAME] -- 대상 파일이름 확인 (name 컬럼)  
2. DBCC SHRINKFILE([LOGFILENAME], 5, TRUNCATEONLY) -- 5MB까지 파일을 축소  
3. -- DBCC SHRINKFILE(DBName_log, 5, TRUNCATEONLY) -- 보통은 이렇게...  

 

 위에서 얘기했듯이, 트랜잭션 로그는 풀백업 시점에서부터 장애 시점까지의 모든 로그가 보관되어 있지 않다면 소용이 없기 때문에, 로그파일을 삭제한 후 장애가 발생하면 장애 시점까지의 복원이 불가능해집니다. 따라서, 트랜잭션 로그를 삭제하여 HDD 공간을 확보한 후에는 반드시 풀 백업을 새로 받는 것이 좋습니다.

 로그를 삭제하지 않고 무조건 SHRINK DATABASE를 한다고 해서 ldf파일의 공간이 줄어들지는 않습니다. 왜냐하면, "SHRINK 는 불필요한 공간을 정리하는 작업"이기 때문입니다. 디스크 조각모음을 시킨다고 해서 필요한 로그를 마음대로 삭제해 버리면 안되겠죠? ^^

 다음과 같은 경우에는 SHRINK 작업을 수행하면 트랜잭션 로그가 삭제되고, .ldf 파일이 축소됩니다. 즉, SQL Server는 다음의 세 가지 경우에는 남아있는 트랜잭션 로그를 무용지물이라고 판단합니다. 세가지 경우의 상황에서 장애가 발생했다고 상상해보세요. 트랜잭션 로그가 있어도 장애 복구에 도움이 되지 않는 다는 것을 알 수 있습니다.

 반대로 다음의 세가지 중 한가지 경우에도 해당이 안된다면 SHRINK 작업을 수행하더라도 .ldf 파일의 사이즈가 줄어들지 않습니다. 왜? 이 트랜잭션 로그가 장애 복구에 필요할 수도 있기 때문이죠. 

① BACKUPLOG 문으로 로그를 지운 경우 : 이미 로그가 백업되었고, 백업된 로그는 내부적으로 삭제되기 때문에 .ldf 파일이 축소될 수 있습니다. WITH NO_LOG 옵션을 준 경우도 마찬가지입니다.

② 복구모델이 SIMPLE인 경우 : 복구모델이 SIMPLE이라는 것은 현 시점의 트랜잭션 로그가 보관되지 않는다는 의미이기 때문에 지나간 트랜잭션 로그도 소용 없다고 판단할 수 있습니다. 

③ 한번도 DATABASE 풀백업을 받은 적이 없는 경우 : 풀백업이 없다면 트랜잭션 로그 보관은 의미가 없습니다.

4. SQL Server의 복구 모델

SQL Server에서는 다음의 세가지 복구모델을 지원합니다. 이 복구모델에 따라 트랜잭션 로그를 기록하고 보관하는 방식이 달라집니다.

1) 전체 (Full)

모든 작업에 대해 트랜잭션 로그가 기록되고, 필요시 복원하거나 원하는 시점까지 복원이 가능합니다. 기본 설정이며, 로그가 가장 많이 기록되고 성능은 가장 떨어집니다.

2) 대량로그 (Bulk Log)

전체 모델과 거의 유사하나 대량의 로그가 기록되는 다음의 작업들에 대해 로그를 남기지 않기 때문에 로그가 더 적게 남고, 성능이 전체 모델보다 더 높습니다.

. SELECT INTO

. BCP 혹은 BULK INSERT

. CREATE INDEX 혹은 INDEXED VIEW

. TEXT 및 이미지 작업

3) 단순 (Simple)

단순 복구모델에서는 CheckPoint (DBMS가 메모리와 HDD 의 Sync를 맞추는 시점) 발생시마다 Sync 후 트랜잭션 로그를 삭제합니다. 따라서, 트랜잭션 로그를 사용한 DB 복원이 불가능하며, 장애 발생시 풀백업 혹은 Differential 백업을 받은 시점까지만 복원이 가능합니다. (BACKUP LOG 문도 실행되지 않습니다.)

만약 DB를 백업받지 않는 서버라면 (장애나면 데이터 포기?? ^^) 단순 복구모델로 설정해도 좋을 듯 합니다. 어차피 풀백업이 없으면 트랜잭션 로그도 소용 없으니까요.

현재 운영중인 DB의 복구모델을 변경할 때는 각 복구모델을 변경한 후에 장애가 발생해도 복구가 가능하도록 하기 위해 복구모델 간의 관계를 잘 따져보아야 하는데 이부분에 대한 자세한 내용은 여기를 참고하세요.