Backup & Recovery Mastery

A database without a backup strategy is just a liability. But a backup strategy is more than just clicking 'Export'. You must choose the right Recovery Model based on how much data your business is willing to lose in a disaster.

1. SIMPLE Recovery

SQL Server automatically clears the transaction log as soon as the data is written to disk. You can only recover to the moment of your **last full backup**. If you backup at midnight and crash at 4 PM, you lose 16 hours of work. Best for Dev/Test or data warehouses.

2. FULL Recovery (Enterprise Standard)

Every transaction is kept in the log until you perform a **Log Backup**. This allows for **Point-in-Time Recovery**. You can restore your database to the exact second (e.g., 2:14:05 PM) before a someone accidentally deleted a table.

3. Transaction Log Tail

In a true disaster, even if your data file (.mdf) is deleted, you can often still back up the 'Tail' of the log (.ldf). This allows you to recover 100% of your data with **Zero Data Loss**, even if your hardware physically died.

4. Interview Mastery

Q: "What is the difference between RTO and RPO?"

Architect Answer: "**RPO (Recovery Point Objective)** is about *Data Loss*: 'How many minutes of work can we lose?' (e.g., 15 minutes of log backups). **RTO (Recovery Time Objective)** is about *Downtime*: 'How long does it take for us to be back online?' (e.g., '2 hours to pull the backup from off-site storage'). A senior architect must negotiate these metrics with the business *before* designing the backup strategy."