All 7 Backup & Recovery questions
More in Backup & Recovery
What are the three main types of backups in SQL Server, and what does each one capture? How do you restore a SQL Server database from a full backup file? How would you perform a point in time restore to recover a database to the exact moment just before an accidental data deletion? How do you automate regular backups in SQL Server so they run consistently without manual effort? How would you design a comprehensive backup and recovery strategy for a large, business critical database with strict recovery time and recovery point objectives? How do you verify that a backup file is not corrupted and can actually be restored successfully?
Advertisement
SQL Server › Backup & Recovery

What are the three recovery models available in SQL Server, and how do they affect your backup strategy?

Intermediate

Answer

The simple recovery model automatically clears the transaction log after each checkpoint and does not support log backups, the full recovery model keeps a complete log of every transaction, allowing point in time recovery, and the bulk logged model is similar to full but minimally logs certain large bulk operations to improve performance during those specific operations.
ALTER DATABASE MyDatabase SET RECOVERY FULL;
Real-world example A hospital's patient records database uses the full recovery model so that if a failure occurs, the database can be restored to the exact moment before the failure, rather than only to the last full or differential backup.

Common follow-ups: Which recovery model is the right default choice for most production databases?;What happens to your ability to recover data if you switch from full to simple recovery model?

Transactions & ACID;Data Types & Schema Design
All 7 Backup & Recovery questions