study/server

[Ubuntu] 06. 홈 서버 운영 - MSSQL 자동 백업과 복구 테스트

렁치 2026. 7. 20. 01:15

자동 배포까지 끝나고 나니 다음으로 신경 쓰인 것은 DB였다.

앱은 GitHub에 코드가 있고 Docker 이미지로 다시 만들 수 있다. 하지만 DB 데이터는 한 번 날아가면 코드처럼 다시 clone할 수 없다.

이전에는 테스트 배포 단계라 DB를 지우고 다시 만들어도 괜찮았다. 하지만 실제 데이터가 쌓이기 시작하면 그런 식으로 작업하면 안 된다. 그래서 이번에는 MSSQL 백업과 복구 과정을 먼저 정리했다.

오늘 목표

오늘 목표는 네 가지였다.

MSSQL 수동 백업 성공
    ↓
백업 파일을 호스트 서버에 저장
    ↓
자동 백업 스크립트 작성
    ↓
백업 파일로 실제 복구 테스트

백업 파일이 생기는 것만 확인하고 끝내고 싶지는 않았다. 실제로 복구까지 해봐야 안심할 수 있다고 생각했다.

현재 MSSQL 구조

MSSQL은 Docker 컨테이너로 실행 중이다.

Ubuntu Server
    ↓
Docker Compose
    ↓
mssql-db
    ↓
SurvivalGame DB

데이터는 호스트 디렉터리에 bind mount로 연결되어 있다.

mssql:
  image: mcr.microsoft.com/mssql/server:2022-CU20-ubuntu-22.04
  container_name: mssql-db
  volumes:
    - ../mssql/data:/var/opt/mssql

컨테이너를 지워도 ../mssql/data에 DB 파일이 남기 때문에 데이터는 유지된다.

하지만 이것만으로는 백업이라고 보기 어렵다.

디스크가 깨지거나, 실수로 데이터를 삭제하거나, 잘못된 Migration이 적용되면 같은 디렉터리 안의 파일만 믿고 복구하기 어렵다.

그래서 별도 백업 파일을 만들어야 한다.

백업 폴더 만들기

먼저 호스트에 백업 폴더를 만들었다.

mkdir -p ~/docker/backups/mssql

그리고 MSSQL 컨테이너에서 이 폴더를 볼 수 있도록 docker-compose.yml에 마운트를 추가했다.

mssql:
  volumes:
    - ../mssql/data:/var/opt/mssql
    - ../backups/mssql:/var/opt/mssql/backup

이렇게 하면 컨테이너 내부의

/var/opt/mssql/backup

이 호스트의

~/docker/backups/mssql

와 연결된다.

변경 후 컨테이너를 다시 만들었다.

cd ~/docker/compose
docker compose up -d --force-recreate mssql
docker compose up -d --force-recreate survivalgame

첫 수동 백업

수동 백업은 sqlcmd로 실행했다.

docker exec -it mssql-db /opt/mssql-tools18/bin/sqlcmd \
-S localhost \
-U sa \
-P "$(grep '^MSSQL_SA_PASSWORD=' ~/docker/compose/.env | cut -d= -f2-)" \
-C \
-Q "BACKUP DATABASE [SurvivalGame] TO DISK = N'/var/opt/mssql/backup/SurvivalGame_test.bak' WITH INIT, COMPRESSION;"

처음에는 SQL Server가 시작 직후라서 이런 오류가 나왔다.

Database cannot be autostarted during server shutdown or startup.

컨테이너가 Running이어도 SQL Server 엔진이 완전히 준비된 것은 아닐 수 있다. 조금 기다린 뒤 DB 상태를 확인했다.

docker exec -it mssql-db /opt/mssql-tools18/bin/sqlcmd \
-S localhost \
-U sa \
-P "$(grep '^MSSQL_SA_PASSWORD=' ~/docker/compose/.env | cut -d= -f2-)" \
-C \
-Q "SELECT name, state_desc FROM sys.databases;"

SurvivalGame이 ONLINE인 것을 확인한 뒤 다시 백업했다.

Permission denied 문제

이번에는 다른 오류가 나왔다.

Cannot open backup device '/var/opt/mssql/backup/SurvivalGame_test.bak'.
Operating system error 5(Access is denied.).

마운트는 되어 있었지만, SQL Server 컨테이너 내부의 프로세스가 호스트 폴더에 파일을 쓸 권한이 없었다.

마운트 상태를 확인했다.

docker inspect mssql-db --format '{{range .Mounts}}{{println .Source "->" .Destination}}{{end}}'

정상적으로 아래처럼 보였다.

/home/<USER>/docker/backups/mssql -> /var/opt/mssql/backup

그러면 남은 문제는 권한이다.

SQL Server Linux 컨테이너는 보통 내부에서 UID 10001 사용자가 실행된다. 그래서 호스트 백업 폴더 권한을 맞췄다.

sudo chown -R 10001:10001 ~/docker/backups/mssql
sudo chmod -R 755 ~/docker/backups/mssql

그 뒤 다시 백업하니 성공했다.

BACKUP DATABASE successfully processed ...

호스트에서도 파일이 보였다.

ls -lh ~/docker/backups/mssql

-rw-r----- 1 10001 10001 560K SurvivalGame_test.bak

자동 백업 스크립트 작성

수동 백업이 됐으니 스크립트로 만들었다.

mkdir -p ~/docker/scripts
nano ~/docker/scripts/backup_mssql.sh

내용은 아래처럼 작성했다.

#!/usr/bin/env bash
set -e

BACKUP_DIR="$HOME/docker/backups/mssql"
DB_NAME="SurvivalGame"
DATE="$(date +%Y%m%d_%H%M%S)"
BACKUP_FILE="${DB_NAME}_${DATE}.bak"

mkdir -p "$BACKUP_DIR"
sudo chown -R 10001:10001 "$BACKUP_DIR"

SA_PASSWORD="$(grep '^MSSQL_SA_PASSWORD=' "$HOME/docker/compose/.env" | cut -d= -f2-)"

docker exec mssql-db /opt/mssql-tools18/bin/sqlcmd \
-S localhost \
-U sa \
-P "$SA_PASSWORD" \
-C \
-Q "BACKUP DATABASE [${DB_NAME}] TO DISK = N'/var/opt/mssql/backup/${BACKUP_FILE}' WITH INIT, COMPRESSION;"

find "$BACKUP_DIR" -name "${DB_NAME}_*.bak" -type f -mtime +56 -delete

echo "Backup completed: $BACKUP_DIR/$BACKUP_FILE"

실행 권한을 부여했다.

chmod +x ~/docker/scripts/backup_mssql.sh

테스트 실행.

~/docker/scripts/backup_mssql.sh
ls -lh ~/docker/backups/mssql

정상적으로 날짜가 붙은 백업 파일이 생성됐다.

SurvivalGame_20260628_233927.bak

주 1회 cron 등록

처음에는 매일 백업도 생각했는데, 지금 단계에서는 과하다고 판단했다.

아직 개인 프로젝트이고 DB 용량도 작다. 서버 디스크도 넉넉한 편은 아니라서 주 1회 자동 백업이면 충분하다고 봤다.

그래서 일요일 새벽 3시에 돌도록 등록했다.

crontab -e

아래 줄을 추가했다.

0 3 * * 0 /home/<USER>/docker/scripts/backup_mssql.sh >> /home/<USER>/docker/backups/mssql/backup.log 2>&1

의미는 이렇다.

0분
3시
매일
매월
일요일

즉 매주 일요일 새벽 3시에 실행된다.

등록 확인.

crontab -l

백업 스크립트 안에서는 56일이 지난 백업을 삭제하도록 했다. 주 1회 기준으로 약 8주치 백업을 보관하는 셈이다.

복구 테스트 준비

백업은 생성보다 복구가 중요하다.

백업 파일이 있어도 복구가 안 되면 사실상 백업이 아니다.

다만 운영 DB인 SurvivalGame을 바로 덮어쓰면 위험하다. 그래서 테스트용 DB 이름으로 복원했다.

운영 DB
SurvivalGame

복구 테스트 DB
SurvivalGame_RestoreTest

먼저 백업 파일 안의 논리 파일명을 확인했다.

docker exec -it mssql-db /opt/mssql-tools18/bin/sqlcmd \
-S localhost \
-U sa \
-P "$(grep '^MSSQL_SA_PASSWORD=' ~/docker/compose/.env | cut -d= -f2-)" \
-C \
-Q "RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/backup/SurvivalGame_20260628_233927.bak';"

결과에서 중요한 값은 LogicalName이다.

SurvivalGame
SurvivalGame_log

이 이름을 RESTORE DATABASE의 MOVE 옵션에서 사용한다.

새 DB 이름으로 복원

운영 DB를 건드리지 않고 테스트 DB로 복원했다.

docker exec -it mssql-db /opt/mssql-tools18/bin/sqlcmd \
-S localhost \
-U sa \
-P "$(grep '^MSSQL_SA_PASSWORD=' ~/docker/compose/.env | cut -d= -f2-)" \
-C \
-Q "
IF DB_ID(N'SurvivalGame_RestoreTest') IS NOT NULL
BEGIN
    ALTER DATABASE [SurvivalGame_RestoreTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [SurvivalGame_RestoreTest];
END;

RESTORE DATABASE [SurvivalGame_RestoreTest]
FROM DISK = N'/var/opt/mssql/backup/SurvivalGame_20260628_233927.bak'
WITH
    MOVE N'SurvivalGame'
        TO N'/var/opt/mssql/data/SurvivalGame_RestoreTest.mdf',
    MOVE N'SurvivalGame_log'
        TO N'/var/opt/mssql/data/SurvivalGame_RestoreTest_log.ldf',
    REPLACE;
"

복원 확인.

docker exec -it mssql-db /opt/mssql-tools18/bin/sqlcmd \
-S localhost \
-U sa \
-P "$(grep '^MSSQL_SA_PASSWORD=' ~/docker/compose/.env | cut -d= -f2-)" \
-C \
-Q "SELECT name, state_desc FROM sys.databases WHERE name LIKE 'SurvivalGame%';"

결과는 이렇게 나왔다.

SurvivalGame              ONLINE
SurvivalGame_RestoreTest  ONLINE

이걸 보고 백업 파일에서 실제 DB 복원이 가능하다는 것을 확인했다.

테스트 DB 삭제

복구 테스트가 끝났으니 테스트 DB는 삭제했다.

docker exec -it mssql-db /opt/mssql-tools18/bin/sqlcmd \
-S localhost \
-U sa \
-P "$(grep '^MSSQL_SA_PASSWORD=' ~/docker/compose/.env | cut -d= -f2-)" \
-C \
-Q "
ALTER DATABASE [SurvivalGame_RestoreTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [SurvivalGame_RestoreTest];
"

운영 DB는 그대로 두고, 복원 테스트용 DB만 잠깐 만들었다가 삭제한 것이다.

최종 백업 구조

현재 백업 구조는 이렇게 정리할 수 있다.

mssql-db 컨테이너
    ↓
BACKUP DATABASE
    ↓
/var/opt/mssql/backup
    ↓ bind mount
~/docker/backups/mssql
    ↓
주 1회 cron 실행
    ↓
56일 지난 백업 삭제

오늘 배운 점

이번 작업에서 가장 크게 느낀 건 백업은 단순히 파일 하나 만드는 일이 아니라는 점이다.

처음에는 BACKUP DATABASE 명령만 실행하면 끝날 줄 알았다. 그런데 Docker 환경에서는 컨테이너 내부 경로와 호스트 경로를 연결해야 하고, Linux 파일 권한까지 맞아야 했다.

특히 10001:10001 권한 문제는 기억해둘 만하다. SQL Server 컨테이너 안에서 돌아가는 사용자가 호스트 폴더에 쓸 수 있어야 .bak 파일이 만들어진다.

그리고 복구 테스트를 직접 해본 것이 가장 중요했다. 백업 파일이 있어도 복구 명령을 한 번도 실행해보지 않으면 실제 장애 상황에서 당황할 수 있다.

이번에는 SurvivalGame_RestoreTest라는 별도 DB로 복원해서 운영 DB를 건드리지 않고 검증했다. 앞으로도 백업 관련 작업은 이런 식으로 안전한 이름을 따로 써야겠다.

다음 작업

이제 백업은 어느 정도 갖춰졌다.

다음은 서버 디스크 관리다.

Docker 로그와 빌드 캐시는 가만히 두면 계속 커질 수 있다. 홈 서버 SSD가 무한한 것도 아니니, 로그 로테이션과 Docker 정리 정책을 잡아둘 생각이다.

저작자표시 비영리 변경금지 (새창열림)