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

SQL서버의 mdf파일이 갑자기 장애가 발생하였을 경우 데이터를 최대한 보존하기 위해 "비상로그백업"을 실시하고

빠른 디비접속을 위한 PRIMARY 파일 그룹을 먼저 복구 후 -> 추가로 부분 파일그룹을 복원하는 방법에 대해서 알아보자.

(스크롤이 좀 길다.)

진행순서

1. 디비 생성 및 백업

2. 데이터베이스를 삭제하여 장애상황 발생

3. 비상로그백업 진행

4. 디비복원

---

1. 디비 생성 및 백업

디비생성

USE [master]
GO

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 [master]
GO

ALTER DATABASE [CrashDB] ADD FILEGROUP [FG1]
GO

ALTER DATABASE [CrashDB] ADD FILE ( NAME = N'FG1_DATA', FILENAME = N'D:\data\CrashDB\FG1_DATA.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG1]
GO

ALTER DATABASE [CrashDB] ADD FILEGROUP [FG2]
GO

ALTER DATABASE [CrashDB] ADD FILE ( NAME = N'FG2_DATA', FILENAME = N'D:\data\CrashDB\FG2_DATA.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG2]
GO

 

테이블 생성(파일 그룹별)

use CrashDB
go

create table T_PRIMARY (col1 varchar(100)) ON [PRIMARY]
GO

create table T_FG1 (col1 varchar(100)) ON [FG1]
GO

create table T_FG2 (col1 varchar(100)) ON [FG2]
GO

 

데이터 입력

insert into T_PRIMARY values ('T_PRIMARY 데이터 입력')
insert into T_FG1 values ('T_FG1 데이터 입력')
insert into T_FG2 values ('T_FG2 데이터 입력')

 

풀백업

BACKUP DATABASE CrashDB to disk = 'D:\data\CrashDB\CrashDB_FULL.bak'

 

데이터 입력(1)

insert into T_PRIMARY values ('T_PRIMARY 데이터 입력(로그백업)')
insert into T_FG1 values ('T_FG1 데이터 입력(로그백업)')
insert into T_FG2 values ('T_FG2 데이터 입력(로그백업)')

 

로그백업

backup log CrashDB to disk = 'D:\data\CrashDB\CrashDB_Log_1.trn'

 

 데이터 입력(2)

insert into T_PRIMARY values ('T_PRIMARY 데이터 입력(비상백업)')
insert into T_FG1 values ('T_FG1 데이터 입력(비상백업)')
insert into T_FG2 values ('T_FG2 데이터 입력(비상백업)')

 

데이터 확인

select * from T_PRIMARY
select * from T_FG1
select * from T_FG2

---

2. 데이터베이스를 삭제하여 장애상황 발생

CrashDB 디비를 오프라인으로 만들고 장애를 발생시키기 위해서 MDF 파일의 이름을 변경합니다.

이후 온라인으로 변경하면 "복구 보류 중" 이라고 디비상태가 변경되면서 장애가 발생합니다.

use master
go
alter database CrashDB set offline
-- delete mdf file 수행(장애 발생했다고 가정 함, MDF 파일 삭제 or 이름변경 진행)
alter database CrashDB set online

---

3. 비상로그백업 진행

데이터 베이스가 복구 보류 중으로 접속이 불가능 하여도 NO_TRUNCATE, CONTINUE_AFTER_ERROR 사용하여 비상 로그백업을 백업 수핼 할 수 있다.

비상로그 백업을 진행합니다.

-- 비상로그백업으로 tb1 테이블의 col컬럼의  '로그백업 후 데이터 입력 값' 이 백업됨
backup log CrashDB to disk = 'D:\data\CrashDB\CrashDB_Log_Emergency.trn' with CONTINUE_AFTER_ERROR

 

---

4. 디비복원

PRIMARY 파일그룹 복원(WITH PARTIAL)

아래에서는 백업 지점을 move to를 사용하여 변경하였다. 

만약 move to 를 지정하지 않으면 백업한 당시와 동일한 경로에 지정한 이름만 변경되서 복원된다.

RESTORE DATABASE CrashDB_RESTORE FILEGROUP='PRIMARY'
from disk = 'D:\data\CrashDB\CrashDB_FULL.bak' WITH NORECOVERY, REPLACE, PARTIAL
,move 'CrashDB' to 'D:\data\CrashDB_restore\CrashDB_restore.mdf'
,move 'CrashDB_log' to 'D:\data\CrashDB_restore\CrashDB_restore_log.ldf'

NORECOVERY 옵션으로 복원하였기 때문에 추가적인 트랜잭선 로그 복원을 대기하고 있는 상태이다.

트랜잭션 로그(로그백업 및 비상로그백업) 2개를 복원하자

 

트랜잭션 로그 복원

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

트랜잭션 로그 마지막 백업본을 복원 시 RECOVERY 옵션으로 복원을 진행한다.

 

데이터 확인

파일그룹 F1,F2는 아직 복원이 되지 안았지만 우선적으로 PRIMARY 그룹을 복원하였다.
현 시점부터 CrashDB_Restore 데이터베이스에 접속되며, PRIMARY 파일그룹 엑세서 가능

use CrashDB_RESTORE
go
select * from T_PRIMARY --정상
select * from T_FG1 --파일그룹 FG1 복원필요
select * from T_FG2 --파일그룹 FG2 복원필요

파일그룹 FG1 복원

use master
go
RESTORE DATABASE CrashDB_RESTORE FILEGROUP='FG1'
from disk = 'D:\data\CrashDB\CrashDB_FULL.bak' WITH NORECOVERY, REPLACE, PARTIAL
,move 'FG1_DATA' to 'D:\data\CrashDB_restore\FG1_DATA.mdf'

 

파일그룹 FG1 트랜잭션 로그 복원

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

 

파일그룹 FG2 복원

use master
go

RESTORE DATABASE CrashDB_RESTORE FILEGROUP='FG2'
from disk = 'D:\data\CrashDB\CrashDB_FULL.bak' WITH NORECOVERY, REPLACE --PARTIAL 옵션을 추가해서 복원하게되면 FG1 파티션 조회불가
,move 'FG2_DATA' to 'D:\data\CrashDB_restore\FG2_DATA.mdf'

 

파일그룹 FG2 트랜잭션 로그 복원

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

 

데이터 확인

use CrashDB_RESTORE
go

select * from T_PRIMARY --정상
select * from T_FG1 --정상
select * from T_FG2 --정상

---

이상으로 SQL Server 파일그룹 부분복원 및 비상 로그 백업 및 복원에 대해서 알아보았다.

포스팅이 좀 길지만 따라서 한번 진행해보면 느낌을 알 수 있다.