Interview Question of the Week #024 – What is the Best Recovery Model?

“What is the best recovery model?” is a common interview question, and the answer still depends on how much work the business can afford to lose and which backups the team actually takes.

Question: Which SQL Server recovery model should I choose?

Answer: SQL Server has SIMPLE, FULL, and BULK_LOGGED. Pick the restore capability and backup routine first, then set the model that supports them.

• Simple: Use it when restoring to the most recent full or differential backup is acceptable. Transaction log backups are not available in this model.
• Full: Use it when you need log backups and a restore path to a particular time covered by the backup chain. The word “full” does not itself prevent data loss; the frequency and success of backups matter.
• Bulk-logged: Consider it for a planned bulk operation that qualifies for minimal logging, while continuing log backups. A log backup containing bulk-logged changes cannot be restored to an arbitrary point within that backup. This is the correction to my old answer, which implied point-in-time recovery was always available.

The original commands have the same form. Choose one model for the named database, not all three in sequence:

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = N'YourDatabase';

-- Choose one only after reviewing the backup plan:
-- ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE;
-- ALTER DATABASE [YourDatabase] SET RECOVERY FULL;
-- ALTER DATABASE [YourDatabase] SET RECOVERY BULK_LOGGED;

Before switching from full or bulk-logged, protect the existing log chain. After switching from simple to full or bulk-logged, take a data backup to start the new log chain and schedule log backups. The answer I want from a candidate is a restore plan, not just the name of a model.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.