English version
SQL Server recovery planning starts with one question: how much data can the business afford to lose?
The answer affects the recovery model, backup schedule, restore procedure, storage layout, and even whether backup traffic should use a separate network path.
The old notes covered recovery models, full/differential/log backups, point-in-time restore, tail-log backups, and why teams sometimes separate backup IPs. The important correction is that backup is not just a command. It is a recovery chain that must be tested.
Recovery models
SQL Server has three recovery models:
| Recovery model | What it means | Transaction log backups | Typical use |
|---|---|---|---|
| Simple | SQL Server truncates inactive log records automatically after checkpoints | Not supported | Dev/test, low data-loss sensitivity, smaller systems |
| Full | SQL Server fully logs transactions and preserves the log chain until log backup | Supported | Production systems that need point-in-time recovery |
| Bulk-logged | Similar to full, but some bulk operations can be minimally logged | Supported, with point-in-time caveats during bulk operations | Large bulk load windows where reduced logging matters |
The recovery model controls how transactions are logged, whether the log can be backed up, and what restore options are available.
Simple recovery is easier to operate, but it cannot use transaction log backups for point-in-time recovery. Full recovery gives stronger recovery options, but only if transaction log backups are taken regularly. If a database is in full recovery model but no log backups are scheduled, the transaction log can keep growing.
Full, differential, and log backups
A practical backup strategy usually combines several backup types.
Full backup captures the whole database at a point in time. It is the base of the restore chain.
Differential backup captures changes since the last full backup. It can reduce restore time because you do not need to apply every later differential, only the latest valid differential after the full backup.
Transaction log backup captures log records since the previous log backup. In the full or bulk-logged recovery model, this is what keeps the log chain alive and allows point-in-time recovery.
A simplified restore sequence looks like this:
Full backup
-> latest differential backup
-> transaction log backup 1
-> transaction log backup 2
-> transaction log backup N
If the target is a point-in-time restore, the final log restore can stop at a specific time, as long as the required log backup contains that point and the log chain is intact.
INIT and backup media safety
The old note mentioned WITH INIT. This option matters because it initializes the backup media set. In practical terms, it can overwrite the existing backup sets on the target media.
That does not mean INIT is always wrong. It means it should be used intentionally, usually with backup rotation and retention designed ahead of time. Accidentally overwriting useful backup history can turn a recoverable incident into a data-loss incident.
Tail-log backup
A tail-log backup captures the tail of the transaction log before a restore operation. It is often used when the database is damaged or needs to be restored and the team wants to capture transactions that have not yet been backed up.
This is one of those operational details that only matters during a bad day. But that is exactly why it should be written into the runbook before the bad day happens.
Backup network separation
Some teams use a separate backup IP or backup network path.
The reason is not SQL Server-specific magic. It is operational isolation.
Large backup files can consume network bandwidth. If backup copy traffic shares the same path as user traffic, reporting jobs, replication, or application connections, a backup window can create latency and throughput problems for the production workload.
A separate backup network can help:
- keep backup copy traffic away from service traffic
- reduce the chance that a large backup transfer saturates the main network path
- make restore, log shipping, or DR copy jobs easier to reason about
- separate operational monitoring for backup traffic
It does not replace good backup testing. A separate network only helps if the backup files are valid, the restore chain is intact, and the team has practiced recovery.
What to test
A backup strategy is not complete until restore is tested.
At minimum, test:
- Can a full backup restore on a clean instance?
- Can the latest differential backup restore after the full backup?
- Can all required log backups restore in order?
- Can the team perform point-in-time restore?
- Are old backup files retained long enough?
- Does backup copy traffic affect the application network?
- Are backup credentials and storage access controlled?
The real deliverable is not “backup job succeeded.” The real deliverable is “we can restore to the required point within the required time.”
Korean notes
SQL Server 복구를 볼 때 핵심 질문은 “얼마나 잃어도 되는가”다.
이 질문에 따라 recovery model, backup 주기, restore 절차, backup file 보관 방식, backup network 분리 여부가 달라진다.
Recovery model
SQL Server에는 세 가지 recovery model이 있다.
- Simple
- Full
- Bulk-logged
Simple recovery model에서는 checkpoint 이후 inactive log가 자동으로 정리된다. 운영은 단순하지만 transaction log backup을 사용할 수 없기 때문에 point-in-time restore에는 적합하지 않다.
Full recovery model은 transaction log chain을 유지할 수 있고 transaction log backup을 통해 point-in-time restore가 가능하다. 대신 log backup을 주기적으로 하지 않으면 transaction log가 계속 커질 수 있다.
Bulk-logged recovery model은 full recovery와 비슷하지만 일부 bulk operation에서 최소 로깅을 사용할 수 있다. 다만 bulk 작업이 포함된 구간에서는 point-in-time restore에 제약이 생길 수 있으므로 운영 정책 없이 막 쓰면 안 된다.
Backup 종류
Full backup은 전체 database를 특정 시점 기준으로 백업한다.
Differential backup은 마지막 full backup 이후 변경된 내용을 백업한다.
Transaction log backup은 이전 log backup 이후의 log record를 백업한다. Full 또는 bulk-logged recovery model에서 log chain을 유지하고 point-in-time restore를 가능하게 만드는 핵심이다.
복원 순서는 보통 이렇게 생각하면 된다.
Full backup
-> Differential backup
-> Transaction log backups in order
중간 log backup이 빠지면 restore chain이 끊길 수 있다. 그래서 backup file이 존재하는지만 볼 게 아니라, 실제로 순서대로 restore되는지 테스트해야 한다.
WITH INIT 주의점
WITH INIT은 backup media를 초기화한다. 쉽게 말하면 기존 backup set을 덮어쓸 수 있다.
운영에서 아예 쓰면 안 된다는 뜻은 아니다. 다만 retention, rotation, restore strategy를 정하지 않고 사용하면 필요한 backup history를 지워버릴 수 있다.
Tail-log backup
Tail-log backup은 restore 전에 아직 백업되지 않은 transaction log의 끝부분을 백업하는 절차다.
장애 상황에서 마지막 변경분을 최대한 보존하고 싶을 때 중요하다. 평소에는 잘 안 쓰는 개념처럼 보여도, 실제 장애 복구 runbook에는 들어가 있어야 한다.
백업 IP를 나누는 이유
백업 IP나 backup network를 분리하는 이유는 production traffic과 backup copy traffic을 분리하기 위해서다.
Backup file은 크기가 크고, 2차 서버나 DR 위치로 복사하는 데 시간이 걸릴 수 있다. 이 트래픽이 application traffic과 같은 네트워크 경로를 쓰면 서비스 latency나 throughput에 영향을 줄 수 있다.
그래서 별도 backup network를 쓰면 다음 장점이 있다.
- backup copy traffic이 서비스 트래픽을 방해할 가능성을 줄임
- log shipping이나 DR copy job을 더 안정적으로 운영
- backup 전용 네트워크 사용량을 따로 관찰 가능
하지만 network를 분리했다고 backup 전략이 완성되는 것은 아니다. 실제로 restore가 되는지, restore 시간이 목표 안에 들어오는지, backup chain이 끊기지 않았는지까지 확인해야 한다.
정리
- Recovery model은 restore 가능 범위를 결정한다.
- Full recovery model은 log backup을 주기적으로 해야 의미가 있다.
- Differential backup은 full backup 이후 변경분이다.
- Transaction log backup은 restore chain과 point-in-time recovery의 핵심이다.
- Backup network 분리는 성능과 운영 안정성을 위한 선택이다.
- Backup 성공보다 restore 성공이 더 중요하다.
Sources
- Microsoft Learn: Recovery models
- Microsoft Learn: Transaction log backups