안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 13-3. 백업과 복원 - 백업과 복원 전략 실행을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 백업과 복원 - 백업과 복원 전략 실행입니다.

TL;DR

단순 복구 모델 복원과정을 설명하며, 전체 백업과 로그 백업이 적용된 전체 복구 모델 백업본 복원 과정도 소개합니다. 특정 시점 복원과 비상 로그 백업 및 복원 절차를 단계별로 설명하며, 백업과 복원 옵션, 데이터베이스 사용자 프로세스 정리, 단일 사용자 모드 작업에 관한 내용도 진행됩니다.

지난 백업과 복원 전략 강좌에서 진행한 단순 복구 모델과 전체 복구 모델을 살펴보겠습니다.

단순 복구 모델

-- 단순 복구 모델 설정
USE master;
GO

-- 데이터베이스 생성
CREATE DATABASE bkupTest;
GO


-- 데이터베이스 복구 모델 확인
SELECT name, recovery_model_desc FROM sys.databases
WHERE name LIKE 'bkupTest';
GO

-- 데이터베이스 복구 모델을 단순 복구 모델로 변경
ALTER DATABASE bkupTest SET RECOVERY SIMPLE;
GO

USE bkupTest;
GO

--테이블 생성
DROP TABLE IF EXISTS RecoveryTest;
CREATE TABLE RecoveryTest(
idx        INT,
dt    DATETIME DEFAULT(GETDATE())
);
GO

-- 데이터를 삽입하고 테스트
INSERT INTO RecoveryTest(idx) VALUES(1);
SELECT * FROM RecoveryTest;
GO

--파일로 전체 백업 -- 초기화 옵션, 10%마다 진행 상태 정보 출력
BACKUP DATABASE bkupTest TO DISK='c:\backup\bkupTest_full_bkup' WITH INIT, STATS=10;
GO

--결과
11 percent processed.
20 percent processed.
30 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
71 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 520 pages for database 'bkupTest', file 'bkupTest' on file 1.
Processed 1 pages for database 'bkupTest', file 'bkupTest_log' on file 1.
BACKUP DATABASE successfully processed 521 pages in 0.025 seconds (162.558 MB/sec).

-- SQL Server SHUTDOWN 수행
SHUTDOWN;

SHUTDOWN을 수행하면, SQL Server 인스턴스가 멈춥니다. 이제 장애 상황을 가정합니다. bkupTest 데이터베이스의 데이터 MDF 파일 - bkupTest.mdf을  삭제합니다.(기본 SQL Server 데이터베이스 설치 경로는 “C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA”입니다.)

SSMS에서 SQL Server 인스턴스를 시작합니다. 좌측 개체 탐색기에서 맨 위의 SQL Server 인스턴스 선택 - 시작하면 됩니다. 예상대로, bkupTest 데이터베이스에 “복구 보류 중” 오류가 발생합니다.

이미지 - 데이터베이스 장애 발생 

데이터베이스 업계에서 가장 두렵다는 공포의 서스펙트 모드(Suspect mode), 혼수상태입니다. 이렇게 데이터베이스 이름이 나오는 이유는 뭘까요? 데이터베이스 이름과 같은 메타 정보는 master 데이터베이스에 있기 때문입니다. mdf - 주 데이터 파일을 삭제했으니 당연히 오류가 발생합니다. 이제 백업본을 이용해 복원을 진행하겠습니다.

-- 데이터베이스 접근 시도
USE bkupTest;
GO

오류 발생 
메시지 945, 수준 14, 상태 2, 줄 2
Database 'bkupTest' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

USE master;
GO

--복원 수행 - 성공
RESTORE DATABASE bkupTest FROM DISK ='c:\backup\bkupTest_full_bkup';
GO

USE bkupTest;
GO

--데이터 조회 가능
SELECT * FROM RecoveryTest;
GO

다시 개체 탐색기의 데이터베이스들을 새로고침 해보면 정상 상태로 돌아온 것을 확인할 수 있습니다.

어떤 백업본을 복원할 수 있었나요? 가장 최근의 전체 백업본까지 복구가 가능합니다. 로그 백업도 불가합니다. 복구 모델이 단순 복구 모델이기 때문입니다. 하지만, 자동으로 로그를 비워주니 로그 저장소가 한없이 커지는 사태는 발생하지 않겠지요. 단순 복구 모델은 혼자 개발하거나 테스트하는 용도가 아니면 거의 사용되지 않고, 대부분 전체 복구 모델을 사용합니다.

다음은 전체 백업과 로그백업을 확인하겠습니다.

전체 복구 모델로 전체 백업과 로그 백업 수행

앞으로 가장 많이 사용하게 될 전체 백업과 로그 백업 수행 방식입니다. 꼭 전체 과정을 이해하시고, 이어지는 강좌 - 유지관리 계획에 전체 백업과 로그 백업 자동화를 수립하세요.

USE master;
GO

-- 데이터베이스 복구 모델 확인
SELECT name, recovery_model_desc FROM sys.databases
WHERE name LIKE 'bkupTest';
GO

-- 데이터베이스 복구 모델이 전체가 아닐 경우 전체로 변경
ALTER DATABASE bkupTest SET RECOVERY FULL;
GO

USE bkupTest;
GO

--테이블을 생성합니다.
DROP TABLE IF EXISTS RecoveryTest;
CREATE TABLE RecoveryTest(
idx        INT,
dt    DATETIME DEFAULT(GETDATE())
);
GO

-- 데이터를 삽입하고 테스트
INSERT INTO RecoveryTest(idx) VALUES(1);
SELECT * FROM RecoveryTest;
GO

--파일로 백업 -- 초기화 옵션, 10%마다 진행 상태 정보 출력
BACKUP DATABASE bkupTest TO DISK='c:\backup\bkupTest_full_bkup' WITH INIT, STATS=10; 
GO

--결과
11 percent processed.
20 percent processed.
30 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
71 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 520 pages for database 'bkupTest', file 'bkupTest' on file 1.
Processed 1 pages for database 'bkupTest', file 'bkupTest_log' on file 1.
BACKUP DATABASE successfully processed 521 pages in 0.025 seconds (162.558 MB/sec).

-- 전체 백업 후 데이터를 삽입하고 테스트
INSERT INTO RecoveryTest(idx) VALUES(2);
SELECT * FROM RecoveryTest;
GO

-- 로그 백업 수행
BACKUP LOG bkupTest 
    TO DISK = 'c:\backup\bkupTest_log_bkup01' WITH INIT;
GO

-- 로그 백업 후 데이터를 삽입하고 테스트
INSERT INTO RecoveryTest(idx) VALUES(3);
SELECT * FROM RecoveryTest;
GO

-- SQL Server 인스턴스 종료.
SHUTDOWN;

자 잘 기억해 두세요. 로그 백업이 어디까지 진행되었나요? 2번 데이터를 넣고 나서 로그 백업을 수행했고, 3번 데이터를 넣었으나, 장애가 발생했습니다. 3번 데이터는 로그 백업에 포함되어 있지 않습니다.

이제 다시 bkupTest 데이터베이스의 데이터파일 MDF를 삭제합니다. 그리고 SSMS에서 SQL Server 인스턴스를 다시 시작하세요. 전체 백업과 로그 백업본 복원을 시작합니다.
 

USE master;
GO

-- 복원 시도 전에 항상 로그 백업을 먼저 시도
-- WITH NO_TRUNCATE 옵션은 데이터베이스 손상 시에도 로그에 접근이 가능하면 로그를 백업하는 옵션.
BACKUP LOG bkupTest 
    TO DISK = 'c:\backup\bkupTest_taillog_bkup' WITH NO_TRUNCATE, INIT;
GO
-- WITH NO_TRUNCATE로 로그 백업 성공
Processed 2 pages for database 'bkupTest', file 'bkupTest_log' on file 1.
BACKUP LOG successfully processed 2 pages in 0.006 seconds (1.953 MB/sec).

-- 전체 백업 복원. NORECOVERY로 이어지는 복원 항목 있음 옵션 설정.
RESTORE DATABASE bkupTest FROM DISK ='c:\backup\bkupTest_full_bkup'
WITH NORECOVERY;
GO

-- 1차 로그 백업 복원. NORECOVERY로 이어지는 복원 항목 있음 옵션 설정.
RESTORE DATABASE bkupTest FROM DISK ='c:\backup\bkupTest_log_bkup01'
WITH NORECOVERY;
GO

-- 2차 장애 후 백업한 로그 백업 복원. RECOVERY로 최종 복원 옵션 설정
RESTORE DATABASE bkupTest FROM DISK ='c:\backup\bkupTest_taillog_bkup'
WITH RECOVERY;
GO

USE bkupTest;
GO

-- 3번 데이터까지 복원, 장애 발생 후 로그를 백업해 복원 가능
SELECT * FROM RecoveryTest;
GO

이렇게, 장애가 발생해도 로그 백업이 가능한 경우라면, 로그를 백업한 다음 복원을 진행해 최대한 많은 데이터를 복원할 수 있습니다. 잊지 마시고, 장애가 발생하면 항상 로그 백업을 먼저 시도하세요.

STOPAT을 이용한 특정 시점 복원(Point in time restore) 수행

13-1. 백업과 복원 - 백업과 복원 이해 강좌에서 자주 받는 질문으로  이런 내용이 있었습니다.
 

괜찮습니다. 흔히 발생하는 경우이니 이렇게 FAQ에도 올라가 있지요. 더 좋은 상황은 로그도 주기적으로 백업하는 것인데 아쉽네요.

전체 백업을 예전에 한번 이상 받았습니다. 한 번도 로그를 백업하지 않았지만, 복구 모델이 전체(FULL)이고, 로그 백업이 가능하다면, 시도해 볼 수 있습니다. 그럼 쿼리로 위의 상황을 수행해 보도록 하겠습니다.

USE master;
GO

-- 데이터베이스 복구 모델 확인
SELECT name, recovery_model_desc FROM sys.databases
WHERE name LIKE 'bkupTest';
GO

-- 데이터베이스 복구 모델이 전체가 아닐 경우 전체로 변경
ALTER DATABASE bkupTest SET RECOVERY FULL;
GO

USE bkupTest;
GO

--테이블을 생성합니다.
DROP TABLE IF EXISTS RecoveryTest;
CREATE TABLE RecoveryTest(
idx        INT,
dt    DATETIME DEFAULT(GETDATE())
);
GO

-- 데이터를 삽입하고 테스트
INSERT INTO RecoveryTest(idx) VALUES(1);
SELECT * FROM RecoveryTest;
GO

-- 전체 백업본이 반드시 1개는 있어야만 STOPAT 복원이 가능합니다.
-- 파일로 전체 백업. INIT 초기화 옵션
BACKUP DATABASE bkupTest TO DISK='c:\backup\bkupTest_full_bkup' WITH INIT;
GO

결과
Processed 520 pages for database 'bkupTest', file 'bkupTest' on file 1.
Processed 1 pages for database 'bkupTest', file 'bkupTest_log' on file 1.
BACKUP DATABASE successfully processed 521 pages in 0.025 seconds (162.558 MB/sec).

-- 전체 백업 후 데이터를 삽입하고 조회 1,2,3 데이터가 존재
INSERT INTO RecoveryTest(idx) VALUES(2);
INSERT INTO RecoveryTest(idx) VALUES(3);
SELECT * FROM RecoveryTest;
GO
-- 마지막 시간을 체크하고 복사.

-- 실수로 WHERE절 없이 모든 테이블 데이터를 UPDATE 함
UPDATE RecoveryTest SET idx = 999;
GO
SELECT * FROM RecoveryTest;
GO

-- 장애 발생. 상황을 팀원들에게 리포트 하고 복원 준비.
-- 우선 UPDATE 작업 실수 직전 시각을 확인해야 함.
-- DATETIME 컬럼을 참고해 확인. 2023-08-04 23:13:47.693


-- 대부분의 경우 놀라서 최근 전체 백업본 복원을 시도함.
USE master;
GO

RESTORE DATABASE bkupTest FROM DISK ='c:\backup\bkupTest_full_bkup';
GO

--복원을 시도하면 비상 로그 백업 오류 발생(Tail-Log Backup error)
메시지 3159, 수준 16, 상태 1, 줄 61
The tail of the log for the database "bkupTest" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
메시지 3013, 수준 16, 상태 1, 줄 61
RESTORE DATABASE is terminating abnormally.

-- SQLER 강좌에서 본 내용을 생각하며, 침착하게 로그 백업 먼저 시도.
-- 복원 구문 실행 전에 로그 백업을 항상 최우선으로 시도하세요. 가장 중요합니다.
BACKUP LOG bkupTest 
    TO DISK = 'c:\backup\bkupTest_taillog_bkup' WITH INIT, NORECOVERY;
GO
-- 비상 로그 백업 성공. 
-- 해당 데이터베이스가 복원 중 상태로 접근 제한 됨.

-- 이제 복원 명령을 수행. 전체 백업을 복원. NORECOVERY로 후속 복원 있음 설정.
RESTORE DATABASE bkupTest FROM DISK ='c:\backup\bkupTest_full_bkup'
WITH NORECOVERY;
GO

-- STOPAT으로 특정 시간 복원 수행. 마지막 복원이니 RECOVERY 옵션 설정.
-- DATETIME에서 기록한 시간 + 1초로 복원.
RESTORE DATABASE bkupTest FROM DISK ='c:\backup\bkupTest_taillog_bkup'
WITH STOPAT = '2023-08-24 20:43:50.353', RECOVERY;
GO

USE bkupTest;
GO

SELECT * FROM RecoveryTest;
GO

결과
idx         dt
----------- -----------------------
1           2023-08-24 20:43:43.660
2           2023-08-24 20:43:49.353
3           2023-08-24 20:43:49.353

(3개 행이 영향을 받음)

이렇게, 전체 백업만 받고 로그 백업은 한 번도 받지 않았지만 장애 상황에서 STOPAT으로 특정 시점 복원을 수행해 피해를 최소화할 수 있습니다.

비상 로그 백업(Tail-Log Backups)

비상 로그 백업은 데이터베이스 복구 모델이 전체(Full) 또는 대량로그(bulk-logged) 일 경우, 아직 백업되지 않은 로그를 백업해 데이터 손실을 최소화하는 작업입니다.

데이터베이스 장애가 발생할 때, 최우선으로 실행해야 하는 작업은 복원 구문 실행이 아니라, 로그 백업입니다. 하지만, 긴장되고 놀라서 복원 먼저 시도하는 경우가 많습니다. SQL Server는 복원을 시도할 경우 경우, 최근 로그 백업이 없다면 위와 같이 비상 로그 백업을 권장하고 최대한 로그를 많이 백업해 복원에 이용할 것을 권장합니다. 만약, 비상 로그 백업 없이 전체 백업만 복원하려면 WITH REPLACE 옵션을 이용해 전체 백업으로 강제 복원도 가능합니다.

복원 소요 시간 기록

항상 백업과 복원에 소요되는 시간을 주의 깊게 살펴보세요. 앞으로 기가 단위를 넘어 테라 단위의 SQL Server 데이터베이스를 다루게 될 수도 있습니다. 백업과 복원에 소요되는 시간을 항상 주의 깊게 살펴보시기 바랍니다.

백업과 복원 옵션

데이터베이스 백업과 복원에 여러 옵션이 있습니다. 위의 예제에서 사용한 옵션들이 일반적으로 사용되는 항목이니 천천히 살펴보시고 개발에 사용하시길 바랍니다.

데이터베이스 사용자 프로세스 정리 및 단일 사용자 모드 작업

복구하기 전, USE master 구문으로 master DB에서 복구를 진행하고, 데이터베이스 사용자를 스크립트로 KILL 하는 이유는 해당하는 DB가 사용 중(in-use)이면 복구가 불가하기 때문입니다. 만약, 사용자를 제어하기 어렵다면, 데이터베이스 사용자 KILL 스크립트를 실행하고, 이후 단일 사용자 모드로 세팅한 다음 진행하는 것도 방법입니다. (이미 데이터베이스 장애가 발생했으니 애플리케이션을 통해 DB 사용자가 붙어도 의미 없겠죠.)

-- 데이터베이스 사용자를 자동 kill 하는 SQL 쿼리.
-- 중요 작업 프로세스가 있는지 미리 체크
USE master
GO

DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('DATABASE_NAME')  -- 데이터베이스 이름으로 변경
EXEC(@kill);
GO

-- 데이터베이스를 단일사용자 모드로 수정
ALTER DATABASE datbase_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

…

--다시 다중사용자 모드로 수정
ALTER DATABASE datbase_name SET MULTI_USER
GO

이제 유지 관리 계획을 수립해 백업과 같은 여러 관리 작업을 자동화시키는 방법을 알아보도록 하겠습니다.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022