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

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

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

TL;DR

백업과 복원의 기본 개념을 다룹니다. 백업은 데이터를 디스크나 디바이스로 복사하는 과정입니다. 데이터베이스 복구 모델에 따라 로그 파일 관리 및 백업 방식이 달라집니다. 로그 파일 백업, 데이터베이스 복구 모델별로 달라지는 백업 방식과 차이점을 소개합니다.

SQLER 분들이라면, 백업(Backup)과 복원(Restore)에 대해 들어 보셨을 거에요. 이번 강좌 챕터를 진행하기 전에, 먼저 질문드립니다.

위 세 가지 작업을 진행하고 설명하는 것이 백업과 복원 챕터의 목표입니다.

백업이란 무엇인가?

백업은 두 가지 의미가 있습니다. 데이터 또는 트랜잭션 로그 데이터를 디스크나 디바이스로 “복사하는 과정”과, 장애 발생 후 데이터를 복구하는 데 사용할 수 있는 “데이터 복사본”을 의미합니다.

간단히, 백업은 데이터를 복사해 둔다는 의미입니다. 물론 그냥 복사만 하는 게 아니라 정확하게, 가능한 많은 데이터를 장애 발생 시 복구하기 위해 데이터 복사본을 만들어 두는 겁니다.

SQL Server는 복구 모델을 제공합니다. 지난 데이터베이스 옵션 강좌에서 짧게 진행했지요. 

이미지 - SQL Server 복구 모델

SSMS에서 데이터베이스를 선택하고, 데이터베이스 속성 -> 옵션 탭을 보면 위와 같은 정보를 볼 수 있습니다. 또는 시스템 카탈로그를 이용해 확인 가능합니다.

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

데이터베이스 로그 

로그가 뭔가요? 데이터베이스 강좌를 진행하면서 데이터 파일과 로그 파일을 생성하고 관리하는 방법을 간략히 말씀드렸습니다. 이 챕터에서는 좀 더 명확히 로그에 대해 살펴봅니다.

데이터 관점에서 확인하는 백업과 복원 관점의 로그

데이터 관점에서 로그는 “데이터 변경 기록”이며 백업과 복원 관점에서 로그는 “데이터 변경이 저장되는 저장소”입니다. 

“데이터 변경 기록-로그”는 변경 정보라서 대부분의 경우 실제 데이터에 비해 로그의 크기가 매우 작습니다. 데이터의 변경보다 대부분의 경우 데이터 조회가 훨씬 더 많으니까요.

로그는 백업하면 비워짐(empty)

“데이터 변경 저장소-로그”는 로그를 백업하면 로그 저장소에서 비워집니다. - 지워진다기보다는 비워진다는 표현이 더 정확합니다. 로그 데이터 저장소 구조는 그대로 유지되고, 오직 로그 데이터만 비워지기 때문입니다. 

만약, 운영하면서 한 번도 이 로그를 백업하지 않았다면 로그 파일이 수기가까지 커져 속도가 느려지고 문제가 발생하는 경우가 종종 발생합니다. 

이전, 4-3. 데이터베이스 크기조절 강좌에서 이미 로그 파일 크기를 줄이는 작업도 진행하셨어요. 아래 이미지는 데이터는 수 메가바이트 정도인데, 로그는 데이터에 비해 매우 커졌습니다.

이미지 - 커진 로그파일

로그는 매우 작은 분량임에도 불구하고, 한 번도 로그를 백업하지 않아 생기는 상황입니다. 사실 개발 과정에서 흔히 발생하는 케이스입니다. 이때, 사용되는 공간도 문제이지만 로그 데이터로 인해 시스템 성능이 저하되는 상황 역시 발생할 수 있습니다.

만약, 관리하는 시스템이 있다면, SSMS에서 데이터베이스 속성을 확인하거나, 아래 SQL 구문을 수행해 로그 파일 크기를 체크할 수 있습니다.

EXEC sp_helpdb '데이터베이스이름'
GO

로그파일을 줄이는 방법은 4-3. 데이터베이스 크기조절 - 데이터베이스 로그파일 줄이기를 참조하세요.

데이터베이스 복구 모델(Recovery model)

SQL Server는 데이터베이스 단위로 복구모델을 설정합니다. 복구 모델은 트랜잭션 로그가 기록되는 방법과 트랜잭션 로그 백업 여부를 설정하고, 복원 시 복구 모델에 맞는 복원 방식을 설정합니다.

단순(Simple) 복구 모델

단순 모델로 설정하면 로그 백업이 불가능하며, 마지막 전체 백업까지 복원 가능합니다. 개인 개발이나 테스트 환경에서 사용할 수 있습니다. 로그를 쌓지 않기 때문에 로그 데이터 파일이 비정상적으로 커지는 상황은 발생하지 않습니다. 

대량로그(Bulk-logged) 복구 모델

로그를 기록하고 백업 가능하지만, 대량작업(BULK INSERT 등)에 대해서는 최소로깅을 수행해 공간을 줄입니다. 지정시점 복원(RESTORE - STOPAT)은 지원하지 않고 가장 최근 로그 백업본까지 복원 가능합니다. 살짝 애매한 백업입니다. 대량작업 빈도에 따라 대량로그 모델을 결정할 수 있지만, 운영환경이라면 가급적 전체 모델을 권장합니다.

☑️ 챗GPT 활용: SQL Server 대량작업(BULK INSERT, BCP)에 대해서 알려줘
☑️ 챗GPT 활용: SQL Server 데이터베이스 지정시점 복원(RESTORE - STOPAT)에 대해서 알려줘

전체(Full) 복구 모델

모든 로그를 기록하고 백업합니다. 지정시간 복원 가능합니다. SQL Server의 모든 백업과 복원 기능을 사용합니다. 특히, 지정시간 복원은 자주 사용될 수 있으니, 가능한 팀단위 개발부터 운영 환경까지 데이터베이스는 전체 복구 모델로 설정하세요.

백업과 복원 관련 FAQ

SQLER에서 자주 받았던 장애 상황과 발생 시 해결 방법을 먼저 기록합니다.

전체백업(X) 로그백업(X) 특정시점 복원 문의

Q. 현재 웹사이트를 개발하는 중입니다. 애플리케이션 개발단계라, 한 번도 백업을 받은 적이 없습니다. 개발자의 실수로 update 회원테이블 set 이름 = '아무개'로 WHERE절 없이 update를 해 버렸습니다. 복원할 방법이 없을까요
A. 복원 불가합니다.

전체백업(O) 로그백업(X) 특정시점 복원 문의

Q. 지속적으로 전체 백업만을 받아 왔습니다. 개발자의 실수로 update 회원테이블 set 이름 = '아무개'로 WHERE절 없이 update를 해 버렸습니다. 복원할 방법이 없을까요?
A. 제약 조건이 있습니다.
- 복구 모델이 전체 복구 모델일 것(DB를 생성하면 기본입니다.)
- 문제 발생 후 풀백업이나 로그에 쓰지 않는 작업을 하지 않았을 것
- 로그 백업이 가능할 것입니다.
만약 이 세 조건에 부합한다면, 바로 로그를 비상로그 백업(Tail-Log Backup) 옵션으로 백업하신 후 RESTORE시 STOPAT 특정 시점 복원 명령으로 복원할 수 있습니다. 다음 강좌를 참고하세요.

전체백업(O) 로그백업(O) 특정시점 복원 문의

Q. 풀백업과 로그백업을 지속적으로 수행하고 있습니다. 개발자의 실수로 update 회원테이블 set 이름 = '아무개'로 WHERE절 없이 update를 해 버렸습니다. 복원할 방법이 있을까요?
A. 가능합니다. 
- 복구 모델이 전체 모델일 것(DB를 생성하면 기본입니다.)
- 문제 발생 후 풀백업이나 로그에 쓰지 않는 작업을 하지 않았을 것

만약 두 가지 조건에 부합한다면, 바로 로그를 비상로그 백업(Tail-Log Backup) 옵션으로 백업하신 후 RESTORE시 STOPAT 특정 시점 명령으로 복원할 수 있습니다. 다음 강좌의 SQL 구문을 참조하세요.

MDF 파일과 LDF 파일만 남았을 때 복원 방법

Q. 이전 SQL Server에서 사용하던 시스템의 디바이스 문제로 해당하는 데이터베이스의 mdf 파일과 ldf 파일만 남았습니다. 새로 설치하는 시스템에서 복원 가능할까요?
A. 이전 서버와 설정이 유사하고 이전에 사용하던 SQL서버가 정상적인 상태로 종료 되었을때 (SQL서버를 스탑 시켰거나 정상적으로 윈도 시스템을 종료시킨 경우 해당 mdf 파일과 ldf 파일이 정상적으로 닫혔을 경우) 복원 가능하며 이때 사용하는 SQL 구문입니다.

CREATE DATABASE 데이터베이스이름  
    ON (FILENAME = N'mdf파일경로'),   
    (FILENAME = N'ldf파일경로')   
    FOR ATTACH
GO

구문으로 복원할 수 있습니다.
이전, 4-2. 데이터베이스 생성 강좌를 참고하세요. 이때, Cross db ownership chaining 문제가 발생할 수 있으니, 위 문서 링크의 가이드 대로 수행하세요.

MDF 파일만 남았을 때 복원 방법

Q. 이전 서버에서 사용하던 시스템의 문제로 해당하는 데이터베이스의 mdf 파일만 남았습니다. 새로 설치하는 시스템에서 복원 가능할까요?
A. 이전 서버와 설정이 유사하고, 이전에 사용하던 SQL서버가 정상적인 상태로 종료되었을  때(SQL서버를 스탑 시켰거나 정상적으로 윈도 시스템을 종료시킨 경우 - 해당 mdf 파일이 정상적으로 닫혔을 경우) 복원 가능하며 이때 사용하는 SQL 구문입니다.

CREATE DATABASE 데이터베이스이름  
    ON (FILENAME = N'mdf파일경로')  
    FOR ATTACH
GO

다른 시스템에의 전체 백업본에서 복원하는 방법

Q. 이전 시스템에서 사용하던 전체 백업본이 있습니다. 새로 시스템을 설치한 후 이 전체 백업본을 신규 시스템에서 복원하려 하는데 방법이 있을까요?
A. 가능합니다.

USE master;
GO

-- Backup 파일의 데이터 파일과 로그파일 정보 출력
RESTORE FILELISTONLY
FROM DISK = N'C:\sql_backup\AdventureWorks2022.bak';
GO

-- 정보에 맞춰 Database Restore 수행
RESTORE DATABASE AdventureWorks
FROM disk= N'C:\sql_backup\AdventureWorks2022.bak'
WITH MOVE N'AdventureWorks2022'
TO N'C:\sql_data\AdventureWorks2022.mdf',
MOVE N'AdventureWorks2022_log'
TO N'C:\sql_data\AdventureWorks2022_log.ldf', 
RECOVERY;
GO

자 이렇게 많이 받는 백업과 복원에 대해 자주 받는 질문을 정리했습니다.

실제 운영 서비스로 클라우드 데이터베이스를 이용하면, 이런 패턴을 사용할 일이 많지 않고, 바로 뒤에서 “유지 관리 계획” 자동화를 이용한 스케줄로 백업이나 유지보수 작업을 진행할 수 있으니 너무 마음 쓰지 않으셔도 됩니다.

다음 강좌에서는 다양한 백업 전략에 대해 소개해 드립니다.
 

SQL 강좌 책 구매

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

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