All jobs / Database administrator
Backup & Restore interview questions
Backup & Restore questions a Database administrator can be asked, from first job to senior. Open one to see the strongest answer and why it works.
What is the difference between a database backup and a storage snapshot?
The strongest answer: A snapshot is a point-in-time image of the volume, fast but tied to that storage; a backup is a consistent copy you can move elsewhere and restore independently — I want both, tested
Why it works: Exactly — snapshots are convenient and quick; backups are portable and independent of the storage that failed.
- Snapshots are fast but local.
- Backups are portable and independent.
- Use both; test both.
- Same server is no protection.
The nightly backup job has run green for eight months; nobody has ever restored from it.
How do you know the backups actually work?
The strongest answer: By restoring them — a scheduled restore to a scratch server with a consistency check, and an alert if the job or the restore fails
Why it works: Exactly — a backup is only proven by a restore, so the restore is the job.
- Only a restore proves a backup.
- Schedule the restore test.
- Alert on both job and restore.
- Do not discover failure in an incident.
Explain full, differential and transaction-log backups, and how they fit together.
The strongest answer: A full is the whole database, a differential what changed since the last full, log backups the changes since the previous log — restore the full, the newest differential, then the logs in order
Why it works: Right — and the restore sequence is the part that matters, because that is what you carry out at two in the morning.
- Full: the whole database.
- Differential: changes since the last full.
- Log: changes since the previous log.
- Restore full, differential, then logs.
A migration script truncated `customer_notes` on production at 10:05; the last full backup ran at 02:00.
You are asked to recover one table someone truncated an hour ago. Approach?
The strongest answer: Restore the backup to a separate database or server, take just that table from there, and copy the rows back — never restore over the live database
Why it works: Exactly — side-by-side restore, then targeted copy, so the rest of the database keeps its last hour.
- Restore to the side, not over.
- Copy back only the lost table.
- Keep the other tables’ writes.
- Truncate is recoverable with backups.
What do you need in place to recover a database to a point in time, say 14:32 yesterday?
The strongest answer: A base backup taken before that moment plus an unbroken chain of transaction or archive logs up to it, stored off the server, with the restore procedure rehearsed
Why it works: Exactly — base plus continuous log chain, and one missing log file breaks everything after it.
- Base backup + continuous logs.
- One gap breaks the chain.
- Store logs off the server.
- Rehearse the restore.
When do you take a logical dump of a database, and when do you take a physical backup?
The strongest answer: Physical backups to restore the same engine fast and roll forward with the log; a logical dump to cross versions or platforms, or to pull back one table — its restore is far slower
Why it works: Exactly — restore time and point-in-time recovery on one side, portability and object-level granularity on the other.
- Physical: fast restore, roll forward.
- Logical: portable, object level.
- Dumps cannot replay the log.
- Match the choice to the RTO.
Backups are written to a second volume on the same server. Adequate?
The strongest answer: No — one server or one account failure loses both; copy backups to separate storage in another location, encrypted, with restore tested from that copy
Why it works: Exactly — separate the copy from every failure the primary shares.
- Separate location.
- Separate account or credentials.
- Encrypt the copy.
- Test restore from the remote copy.
Point-in-time recovery has worked for a year. This morning the log backups start failing, and the newest usable one is from yesterday evening.
What has probably happened, and what do you do?
The strongest answer: Something broke the chain — a switched recovery model, a restore or reattach, or deleted log files — so I take a fresh full to start a new chain, then find which and stop it
Why it works: Right — re-establish the chain first because you are exposed right now, then do the forensics so it is not a monthly event.
- Re-establish the chain first.
- Take a fresh full now.
- Then find what broke it.
- Alert on gaps, not only failures.
The business has signed off a one-hour recovery objective. A test restore of the main database, several terabytes, takes most of a working day.
How do you close the gap?
The strongest answer: Measure the real restore rate, then buy the time back — a standby already restored, critical objects first, storage snapshots with log replay — and take any remaining gap to the business in writing
Why it works: Exactly — the objective is met by not restoring from scratch, and any residual gap has to be a decision someone signed rather than a surprise.
- Measure the real restore rate.
- Keep restores off the critical path.
- Restore what the business needs first.
- Put the residual gap in writing.
How do you make backups survive a ransomware attack that compromises the database servers and their credentials?
The strongest answer: Copies in a separate account or vault with immutability or write-once retention, credentials the database servers do not hold, encryption keys managed apart, and restores tested from that isolated copy
Why it works: Exactly — a copy the attacker cannot reach or alter, proven by a restore.
- Isolated account or vault.
- Immutable retention.
- Credentials the servers lack.
- Restore from the isolated copy.