SQL Server Interview Question #190

What are SQL Server recovery models?

Security, Backup, Recovery, Administration & Real-World Scenarios Senior Advanced

Detailed Explanation

SQL Server provides SIMPLE, FULL, and BULK_LOGGED recovery models.

SIMPLE automatically reuses log space after checkpoints when it is no longer required and does not support the normal transaction-log backup chain used for point-in-time recovery. FULL supports log backups and point-in-time recovery when the backup chain is maintained. BULK_LOGGED is similar to FULL but can reduce logging for certain bulk operations, with recovery implications during affected log backups.

The model should be chosen from business recovery requirements, not merely database size.

Code Example

SELECT name, recovery_model_desc
FROM sys.databases;