SQL Server Recovery Models — Logging Behavior Under which recovery model are all changes logged except those operations that would create very large log entries (e.g., bulk operations)?

Introduction:
Recovery models determine how transactions are logged and what restore strategies are available. Choosing the proper model balances performance, log growth, and recoverability requirements.

Given Data / Assumptions:

• Bulk operations can generate massive log records.
• The question asks which model minimally logs those bulk operations while still logging everything else.

Concept / Approach:
Bulk-logged recovery provides minimal logging for qualifying bulk operations (e.g., BULK INSERT, SELECT INTO, certain index operations) while fully logging other transactions. This reduces log size during heavy loads at the cost of losing point-in-time recovery if a bulk operation occurred since the last log backup.

Step-by-Step Solution:
1) Compare recovery models: FULL logs everything; SIMPLE truncates log on checkpoint; BULK-LOGGED minimally logs bulk operations.2) Identify the one that exempts only very large operations from full logging.3) Select bulk-logged recovery.

Verification / Alternative check:
Review restore capabilities: after bulk operations in bulk-logged model, you cannot do point-in-time restore across those operations.

Why Other Options Are Wrong:

• Differential recovery: Not a recovery model; differential refers to a backup type.
• Full recovery: Everything is fully logged.
• Simple recovery: Truncates log regularly; not “all changes except big ones.”
• Point-in-time only recovery: Not a defined model.

Common Pitfalls:
Confusing backup types (full, differential) with recovery models, and assuming simple equals minimal logging for bulk operations.

Final Answer:
Bulk-logged recovery