What backup types does SQL Server have, and what is point-in-time recovery?
Quick Answer
Three backup types: Full (the whole database), Differential (changes since the last full), and Transaction Log (all log records since the last log backup). To restore to a specific moment you restore the last full, the latest differential, then the chain of log backups WITH STOPAT = '...'. Point-in-time recovery requires the Full (or Bulk-Logged) recovery model.
There's a full backup of the whole database, a differential which is everything changed since the last full, and log backups which capture the transaction log. To do point-in-time recovery you restore the last full, then the most recent differential, then replay the log backups up to the exact moment you want with STOPAT. That only works if the database is in the Full recovery model — in Simple the log gets truncated and you can only go back to a full or differential. How often you take log backups comes down to your RPO, how much data loss is acceptable. And you test the restores, because a backup you've never restored is a guess.
- Recovery models: Simple — log is auto-truncated, no log backups, no point-in-time recovery. Full — log kept until backed up, enables point-in-time. Bulk-Logged — like Full but bulk ops are minimally logged.
- RPO (how much data you can afford to lose) drives log-backup frequency; RTO (how fast you must be back) drives differential frequency and infrastructure.
- A broken log chain (a missing log backup, or switching to Simple and back) means you can only recover to the last full/diff.
- Always test restores — an untested backup isn't a backup.