SQL Server 백업 전략, 자동화 및 복원

설치된 SQL Server instance(Linux 또는 Windows; 단일 instance, FCI 또는 AG replica)의 백업을 구성하고 백업에서 복구합니다. 백업: 복구 모델 선택, 주간 full + 일간 differential + n분마다 로그 백업, COMPRESSION, CHECKSUM, 인증서 기반 암호화, 보존 기간과 일정(SQL Server Agent T-SQL job 또는 Ola Hallengren; Express에서는 Task Scheduler / cron), 일회성 COPY_ONLY 백업, 백업 검증 및 체인 점검. 복구: full / differential / 로그 체인 복원, STOPAT을 이용한 특정 시점 복원(PITR), 표시된 트랜잭션, WITH MOVE로 다른 이름이나 다른 서버에 복원, 데이터 파일 손실 시 tail-log + 복원, 페이지 복원, master / msdb / model 복원, 로그인 매핑(분리된 사용자) 및 암호화된 백업의 인증서 로드.

수행할 작업을 시나리오 카드에서 선택합니다. 오류 코드(예: 824, 4305, 33111, 18456)를 카탈로그 검색에 입력하면 해당 시나리오로 바로 이동할 수 있습니다. 테이블이나 쿼리 결과를 파일로 내보내려면(백업이 아니라 데이터 이동) bcp로 내보내기를 참고하십시오.

구성: 서버 1대, Failover Cluster Instance 또는 Always On AG replica(다른 서버로 복원 시: 원본 + 대상)

시나리오

• 백업 전략 구성(예약 백업 + 보존 기간) — 새 서버 또는 정기 백업이 없는 데이터베이스: full / differential / 로그 job, 보존 기간과 모니터링을 구성합니다.
• 일회성 백업(예: 변경 전 COPY_ONLY) — 변경, patch 또는 upgrade 전이나 테스트 환경 복사용 단일 백업입니다. COPY_ONLY는 체인을 깨뜨리지 않습니다.
• 백업 검증(기록, VERIFYONLY, 테스트 복원) — 백업이 실제로 열리는지 입증하기 위한 절차: 백업 기록, VERIFYONLY, HEADERONLY / FILELISTONLY, 임시 데이터베이스에 테스트 복원 + CHECKDB.
• 백업 체인 무결성 점검(LSN 공백, 누락 파일) — 복원 전이나 정기 점검 시: msdb 기록에서 체인을 추출하여 LSN 공백, 디스크에 없는 파일, 중간에 끼어든 백업을 찾습니다. 아무것도 변경하지 않습니다.
• 백업에서 데이터베이스 복원(체인, STOPAT / STOPATMARK, MOVE / REPLACE, 다른 서버) — 데이터가 삭제되었거나 잘못 업데이트됨, 데이터베이스 복사본이 필요함 또는 다른 서버로 이전 예정이며 instance는 실행 중인 경우입니다. full + differential + 로그 체인, 특정 시점 또는 표시된 트랜잭션, 다른 이름 / 디렉터리, tail-log로 마지막 시점까지.
• 데이터베이스가 열리지 않음: 데이터 파일 손실 또는 손상(tail-log + 복원) — 데이터베이스가 RECOVERY_PENDING 또는 SUSPECT이고 .mdf/.ndf를 읽을 수 없지만 instance는 실행 중인 경우: 먼저 NO_TRUNCATE로 tail-log를 백업한 다음 마지막 시점까지 복원합니다. 작업이 끝날 때까지 데이터베이스는 사용할 수 없습니다.
• 일부 페이지 손상(Msg 823 / 824): 페이지 복원 — 쿼리나 CHECKDB가 특정 페이지에서 오류를 냄(msdb.dbo.suspect_pages): 손상된 페이지만 백업에서 복원합니다. Enterprise/Developer에서는 데이터베이스가 온라인 상태를 유지하고, Standard/Express에서는 잠시 오프라인이 됩니다.
• master 데이터베이스 복원(단일 사용자 모드) — master 손상 또는 로그인 / linked server / 서버 설정 손실: instance를 단일 사용자 모드(-m)로 시작하고 master를 복원하면 SQL Server가 스스로 종료됩니다. 다운타임이 필요하며, instance가 전혀 시작되지 않으면 먼저 시스템 데이터베이스를 다시 빌드합니다.
• msdb 또는 model 데이터베이스 복원 — job, 백업 기록, Database Mail(msdb) 또는 새 데이터베이스 템플릿(model)이 손상 / 손실된 경우: 같은 빌드에서 만든 백업에서 REPLACE로 복원합니다. Agent가 잠시 중지됩니다.
• 복원 후 사용자가 접속하지 못함(분리된 사용자, 로그인 매핑) — 데이터베이스를 다른 서버로 옮겼거나 복원했고 애플리케이션에 'Login failed'가 발생하는 경우: 로그인을 SID와 비밀번호 해시 그대로 원본에서 이전하고, 남은 사용자는 로그인에 연결합니다. 데이터베이스는 온라인 상태를 유지합니다.
• 암호화된 백업이 열리지 않음(인증서 없음, Msg 33111) — 백업(또는 TDE 데이터베이스의 백업)이 인증서로 암호화되어 있고 이 서버에 인증서가 없는 경우: thumbprint를 찾고 인증서를 private key와 함께 로드한 뒤 백업을 검증합니다. 기존 데이터베이스는 건드리지 않습니다.

시작하기 전에

• RPO(허용 가능한 데이터 손실)와 RTO(허용 가능한 다운타임)를 업무 부서와 문서로 합의했습니다. 선택한 계획: …최대 …분 데이터 손실…마지막 full/differential 백업 시점으로 복원(SIMPLE: 최대 하루치 데이터 손실)…; 복원 소요 시간은 가장 큰 데이터베이스의 테스트 복원으로 측정합니다.
• SQL Server가 실행 중이고 mssql-tools18(sqlcmd)이 설치되어 있으며 …은 sysadmin입니다. 스크립트를 실행하기 전에 비밀번호를 화면에 표시하지 않고 변수에 저장합니다: read -rsp '비밀번호: ' SQLCMDPASSWORD; export SQLCMDPASSWORD 그리고 sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh.
• PowerShell을 관리자 권한으로 열었고(시작 → "PowerShell" → 마우스 오른쪽 버튼 → Run as administrator; 제목 표시줄에 Administrator:) SQL Server에서 sysadmin인 Windows 계정을 사용합니다. sqlcmd가 PATH에 있습니다.
• 백업 디렉터리(…)가 데이터 디스크와 다른 디스크에 있고, 최소 1주일치 백업을 담을 여유 공간이 있습니다(대략: 사용 중인 데이터 크기 × 2 + 일간 변경량 × 일수, 압축 시 보통 3–5배 적음).
• 백업 디렉터리(…)가 데이터 디스크와 다른 디스크에 있고, 최소 1주일치 백업을 담을 여유 공간이 있습니다(대략: 사용 중인 데이터 크기 × 2 + 일간 변경량 × 일수, 압축 시 보통 3–5배 적음).
• 데이터베이스 크기를 파악했고, 백업 윈도(full 백업 소요 시간)가 업무 시간과 겹치지 않습니다.
• 백업을 서버 외부(다른 서버, NAS, 오브젝트 스토리지, 백업 소프트웨어)로 복사하는 방법을 정했습니다. 같은 디스크에 있는 백업은 디스크나 서버를 잃으면 쓸모가 없습니다.
• 복원할 SQL Server 버전이 백업을 만든 버전과 같거나 더 최신입니다. 더 최신 버전에서 만든 백업은 이전 버전에 복원할 수 없습니다(예: 2022 → 2019 불가).
• 애플리케이션 팀에 알리고 애플리케이션을 중지했습니다. 덮어쓰기 복원 대상 데이터베이스의 모든 연결이 끊기며, 복원 중에는 데이터베이스를 사용할 수 없습니다.
• 대상에 데이터(…) 및 로그(…) 디렉터리가 있고 소유자는 mssql이며, 복원할 데이터베이스의 파일 크기만큼 여유 공간이 있습니다(백업 크기가 아니라 FILELISTONLY의 Size 값 기준).
• 대상에 데이터(…) 및 로그(…) 디렉터리가 있고 SQL Server 서비스 계정에 쓰기 권한이 있으며, 복원할 데이터베이스의 파일 크기만큼 여유 공간이 있습니다.
• 대상 서버(…)에서 원본(…)에 …SSH(root 또는 sudo 권한 사용자)로…SMB 관리 공유(…)로… 접근할 수 있으며 백업 파일을 복사할 수 있습니다.

가이드 단계

• 현재 상태 — 데이터베이스 및 최근 백업 나열
• 백업 전략 — 백업 디렉터리 준비 · 백업 디렉터리 준비 · 복구 모델 설정(…) · AG 백업 기본 설정 구성(…) · 기본 설정 replica 확인 · 인증서 생성 및 백업(암호화) …
• 일회성 백업 — …COPY_ONLY full 백업 수행…Full 백업 수행…Differential 백업 수행…로그 백업 수행…(…) · 백업 기록 확인(msdb.dbo.backupset)
• 복원 — 백업 기록에서 복원 체인 찾기 · 백업 파일을 대상 서버로 복사 · 백업 파일을 대상 서버로 복사 · 대상 서버에 인증서 로드 · 대상 서버에 인증서 로드 · 백업 파일 검사(VERIFYONLY, HEADERONLY, FILELISTONLY) …
• 백업 검증 — 백업 기록 확인(msdb.dbo.backupset) · 백업 파일 검사(VERIFYONLY, HEADERONLY, FILELISTONLY) · 테스트 복원 및 DBCC CHECKDB 실행
• 백업 체인 점검 — 진단: 체인 상태(데이터베이스 + msdb 기록) · 백업 파일이 디스크에 있는지 확인 · 백업 파일이 디스크에 있는지 확인 · 체인의 모든 백업 검증(VERIFYONLY) · 후속 조치: 체인이 끊겼으면 새 full 백업 수행
• 데이터 파일 손실 / 손상: tail-log + 복원 — 진단: 데이터베이스와 파일 상태 · 진단: 파일이 디스크에 있는지, 로그 파일이 정상인지 · 진단: 파일이 디스크에 있는지, 로그 파일이 정상인지 · 안전장치: 남은 파일 복사 · 안전장치: 남은 파일 복사 · tail-log 백업 수행(NO_TRUNCATE) …
• 페이지 복원(손상된 페이지) — 진단: 손상된 페이지 찾기(suspect_pages, CHECKDB) · 진단: 페이지가 속한 테이블 / index · 안전장치: 로그 백업 수행, 로그 백업 job 중지 · msdb 기록에서 복원 순서 추출(PAGE) · tail-log 백업 수행(NORECOVERY): 데이터베이스 오프라인 · 손상된 페이지 복원 및 로그 백업 적용(NORECOVERY) …
• master restore — 진단: 백업 파일, 버전, 손실될 항목 · 진단: SQL Server가 시작되지 않는 이유 · 진단: SQL Server가 시작되지 않는 이유 · 시스템 데이터베이스 다시 빌드(master가 시작되지 않음) · 시스템 데이터베이스 다시 빌드(master가 시작되지 않음) · SQL Server를 단일 사용자 모드로 시작 …
• msdb / model 복원 — 진단: 상태, 버전, 백업 파일 · 안전장치: 현재 … 데이터베이스 백업 · SQL Server Agent 중지 · … 데이터베이스 복원(REPLACE) · SQL Server Agent 시작 · 검증
• 로그인 매핑(분리된 사용자) — 진단: 분리된 사용자 찾기(대상) · 원본에서 로그인 스크립트 생성(SID + 비밀번호 해시) · 원본에서 로그인 스크립트 생성(SID + 비밀번호 해시) · 로그인 스크립트를 대상에 복사하여 실행 · 로그인 스크립트를 대상에 복사하여 실행 · 남은 사용자를 로그인에 연결(ALTER USER WITH LOGIN) …
• 암호화된 백업: 인증서 로드 — 진단: 이 서버의 인증서와 master key · 진단: 인증서 파일의 thumbprint · 진단: 인증서 파일의 thumbprint · 인증서를 private key와 함께 로드 · 인증서를 private key와 함께 로드 · 검증: 백업을 읽을 수 있음 …