What is RPO (Recovery Point Objective), and what backup lever usually drives it?
RPO is how much committed data change you can afford to lose at recovery (“no older than X”). It is driven mainly by how often you capture recoverable state — typically transaction log backup frequency (plus replication/snapshot design if those apply).
What is RTO (Recovery Time Objective), and why is it not solved by backups alone?
RTO is how long you may take to bring the service back (restore, failover, DNS, validation). Backups set a lower bound on restore work, but RTO depends on hardware, size, runbooks, HA path (e.g. AG failover vs cold restore), and tested procedures.
In FULL recovery model, what happens to the transaction log until you back it up?
Changes are fully logged; log records stay until they are no longer needed for recovery — in practice you back up the log to capture work and allow truncation/reuse of log space (after the backup completes for normal log backups).
SIMPLE recovery model: can you take transaction log backups? Can you restore to an arbitrary point in time?
No log backups. No point-in-time restore to an arbitrary time — only to the end of the full or differential you restore (backup boundaries).
Why might you briefly use BULK_LOGGED on a production database that is normally FULL?
Select bulk operations can be minimally logged, reducing log volume during large loads — often switch in, run the load, switch back to FULL after a log backup. Tradeoff: PITR during the bulk-logged interval has limitations (per Microsoft docs).
What does a transaction log backup capture relative to the previous log backup, and what two jobs does it do for FULL/BULK_LOGGED?
It captures log records since the prior log backup (incremental at the log layer). It protects recent committed work in the chain and, after backup, helps truncate the log so it does not grow without bound.
Differential backup: changed since when? What resets the differential base?
Extents changed since the last full (not since the last diff). A new non–copy-only full resets the differential base; without new fulls, diffs grow over time.
What does BACKUP ... WITH COPY_ONLY on a full backup avoid doing?
It does not reset the differential base — so scheduled full/diff math for production recovery stays valid (ad hoc / vendor / sandbox copy without breaking the chain).
Typical restore order for point-in-time recovery from native backups?
FULL → optional latest DIFF → LOG backups in LSN/chronological order → final step with RECOVERY (and STOPAT / STOPATMARK on the appropriate log restore for PIT).
WITH NORECOVERY vs WITH RECOVERY on restore — when do you use each in a chain?
NORECOVERY: apply the backup but leave the DB in RESTORING so more backups can be applied — use on all but the last step. RECOVERY: finish redo/undo, bring DB ONLINE — last step (or run recover alone on a DB still restoring).
On an Always On Availability Group, what function should backup jobs call so only the preferred replica runs a given backup?
sys.fn_hadr_backup_is_preferred_replica('DatabaseName') — deploy the same job on replicas that might backup, gate the step with this (per Microsoft guidance).
What does AUTOMATED_BACKUP_PREFERENCE = SECONDARY mean, and what risk does SECONDARY_ONLY create?
SECONDARY: prefer a secondary when eligible (common to offload I/O). SECONDARY_ONLY: backups only on secondaries — if no secondary is eligible, no backup runs unless you have a fallback design.
For SQL Server versions before 2025, which backup types can run on a secondary replica vs primary?
Log backups: yes on secondary (normal chain). Full: only COPY_ONLY full on secondary; non–copy-only full on primary. Differential: not on secondary — primary only. (2025+ Microsoft expanded secondary support — confirm version in interview.)
Why is “weekly full + daily diff + log every 5–15 minutes” a common starting template, not a universal rule?
It matches many FULL-model OLTP RPO goals, but real design depends on RPO/RTO, diff growth vs full age, backup success monitoring, restore testing on large DBs, and I/O impact — diffs balloon if fulls slip.
RDS SQL Server Multi-AZ vs EC2 self-managed AG: who owns backup cadence, and what’s the SQL Server–specific backup/I/O caveat on RDS?
On RDS, AWS drives automated backups, retention, backup window, and PITR inside retention — not your WSFC/fn_hadr job design. SQL Server on RDS: backup is from the primary with brief I/O suspend — Multi-AZ does not mean zero backup impact (unlike some other RDS engines).
