Question: What is the backup timeline for restoring a database?
Answer: Backup and restore questions still trip up experienced DBAs. The differential backup is usually where the explanation goes astray, so I draw a timeline before naming any files.
In the full recovery model, a common sequence is a full backup, later differential backups, and transaction log backups between and after them. The timeline below makes the choice visible:
Suppose I need to restore to the end of this timeline. I restore the full backup, then Differential 2 if it is based on that full backup. I don’t restore Differential 1 first. Next I restore the log backups taken after the second differential, in log-chain order, and recover the database at the end. The earlier log backups are not replayed separately, because the chosen differential has already brought the database forward.
That’s the interview answer, but a real restore needs two checks before touching production: confirm the differential’s base full backup, and confirm an unbroken log chain through the desired recovery point. If the goal is the point of failure, a tail-log backup may also be needed. Keep the database in NORECOVERY until the final restore step.
The short rule is matching full, latest usable differential, then the required logs in order.
A restore is not a replay of every backup, it is the shortest unbroken path from the full backup to the moment you need.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
