[MSSQL] 비상 로그 백업 및 복원(MDF 파일 장애 발생-복구 보류 중)

MSSQL 디비 서버의 mdf 파일이 갑자기 장애가 발생하였을 경우 데이터를 최대한 보존하기 위해 비상로그백업을 실시하고 복원하는 방법에 대해서 알아보자.

 

1. 디비생성 및 백업

디비 생성 구문

--디비생성
CREATE DATABASE CrashDB
ON  PRIMARY ( NAME = N'CrashDB', FILENAME = N'D:\data\CrashDB\CrashDB.mdf' , SIZE = 10MB)
LOG ON ( NAME = N'CrashDB_log', FILENAME = N'D:\data\CrashDB\CrashDB_log.ldf' , SIZE = 10MB)
GO

데이터 입력 및 백업

--데이터 입력 및 백업
use CrashDB
go
create table tb1 (col1 varchar(1000))
insert into tb1 select '풀백업 전 초기상태'

BACKUP DATABASE CrashDB to disk = 'D:\data\CrashDB\CrashDB_FULL.bak'
insert into tb1 select '풀백업 후 데이터입력 값'

backup log CrashDB to disk = 'D:\data\CrashDB\CrashDB_Log_1.trn'
insert into tb1 select '로그백업 후 데이터 입력 값'

 

데이터 확인

--데이터 확인
아래 조회되는 '로그 백업 후 데이터 입력 값' 은 백업이 안되어 있는 상태.
(3. 비상로그백업 진행 에서 "비상로그백업"을 진행할 예정)
use CrashDB
go
select * From tb1

 

---

2. MDF 파일 장애발생

MDF 파일 이름변경(장애발생)

use master
go
alter database CrashDB set offline

 

delete mdf file 수행(장애 발생했다고 가정 함, MDF 파일 삭제 or 이름변경 진행)

alter database CrashDB set online --MDF파일 이름 변경으로 온라인 불가

 

물리적 파일 "D:\data\CrashDB\CrashDB.mdf"을(를) 열 수 없습니다. 운영 체제 오류 2: "2(지정된 파일을 찾을 수 없습니다.)".

데이터베이스 "CrashDB"을(를) 다시 시작할 수 없습니다. 이전 상태로 돌아갑니다.

ALTER DATABASE 문이 실패했습니다.

---

3. 비상로그백업 진행

-- NO_TRUNCATE, CONTINUE_AFTER_ERROR 사용하여 백업 수행
backup log CrashDB to disk = 'D:\data\CrashDB\CrashDB_Log_Emergency.trn' with no_truncate -- 비상로그백업으로 tb1 테이블의 col컬럼의  '로그백업 후 데이터 입력 값' 이 백업됨
--backup log CrashDB to disk = 'D:\data\CrashDB\CrashDB_Log_2.trn' with CONTINUE_AFTER_ERROR

 

---

4. 디비복원

풀백업 복원(복원중)

--풀백업 복원(복원중)
--복구상태 : RESTORE WITH NORECOVERY(데이터베이스를 비작동 상태로 유지하고 커밋되지 않은 트랜잭션을 롤백하지 않습니다. 추가 트랜잭션 로그를 복원할 수 있습니다.)
RESTORE DATABASE CrashDB_Restore from disk = 'D:\data\CrashDB\CrashDB_FULL.bak'
with move 'CrashDB' to 'D:\data\CrashDB\CrashDB_restore.mdf'
, move 'CrashDB_log' to 'D:\data\CrashDB\CrashDB_restore_log.ldf'
, norecovery

파일 1에서 데이터베이스 'CrashDB_Restore', 파일 'CrashDB'에 대해 376개의 페이지를 처리했습니다 .

파일 1에서 데이터베이스 'CrashDB_Restore', 파일 'CrashDB_log'에 대해 2개의 페이지를 처리했습니다 .

RESTORE DATABASE이(가) 378개의 페이지를 0.102초 동안 처리했습니다(28.913MB/초).

로그백업 복원(복원중)

RESTORE LOG CrashDB_Restore from disk = 'D:\data\CrashDB\CrashDB_Log_1.trn' with norecovery

파일 1에서 데이터베이스 'CrashDB_Restore', 파일 'CrashDB'에 대해 0개의 페이지를 처리했습니다 .

파일 1에서 데이터베이스 'CrashDB_Restore', 파일 'CrashDB_log'에 대해 4개의 페이지를 처리했습니다 .

RESTORE LOG이(가) 4개의 페이지를 0.004초 동안 처리했습니다(6.835MB/초).

 

비상 로그백업 복원(복원중)

RESTORE LOG CrashDB_Restore from disk = 'D:\data\CrashDB\CrashDB_Log_Emergency.trn' with norecovery

파일 1에서 데이터베이스 'CrashDB_Restore', 파일 'CrashDB'에 대해 0개의 페이지를 처리했습니다 .

파일 1에서 데이터베이스 'CrashDB_Restore', 파일 'CrashDB_log'에 대해 5개의 페이지를 처리했습니다 .

RESTORE LOG이(가) 5개의 페이지를 0.004초 동안 처리했습니다(8.789MB/초).

 

디비 복원

--디비 복원
--비상로그 백업까지 복원이 완료 되었으니 디비를 복원한다.
RESTORE DATABASE CrashDB_Restore with recovery

RESTORE DATABASE이(가) 0개의 페이지를 0.536초 동안 처리했습니다(0.000MB/초).

 

데이터 확인

비상로그복원 후 데이터를 확인한다.

-- 비상로그복원 후 데이터를 확인한다.
use CrashDB_Restore
go
select * From tb1

이상으로 MSSQL MDF파일 장애 발생시. 데이터 손실을 최소화 하기위한 비상로그 백업을 받는 방법과 디비 복원 방법에 대해서 알아보았습니다.