SQL Interview: 104 Backup restore time
This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.
Section: Administration Level: Advanced
Question:
A database backup was started at 12:00 PM. It completed at 2:00 PM.
When the backup is restored, at what time point will the restored data be?
- 12:00 PM
- 2:00 PM
- Somewhere between 12:00 PM and 2:00 PM
Answer:
A substantial amount of documentation suggests that the time would be 2:00 PM, however the real answer is Somewhere between 12:00 PM and 2:00 PM
It’s because of a subtle but very important detail about how SQL Server backups work.
When you take a full database backup, SQL Server:
- Notes the starting Log Sequence Number (LSN) at the moment the backup begins (12:00 PM in the example).
- Reads all data pages in the database as they exist at the time they’re read — this can take minutes or hours depending on database size and activity.
- Captures transaction log records for any changes that occur during the backup, ensuring the backup is transactionally consistent at a single point in time.
- Notes the ending LSN when the backup completes (2:00 PM here).
When you restore this backup:
- SQL Server restores the data pages as they were read, plus
- applies the logged changes up to the point of the backup’s internal checkpoint — i.e., a consistent point somewhere between the start and the end of the backup.
So, the restored database represents the state of the data as of a single consistent moment between 12:00 PM and 2:00 PM — not exactly at either endpoint.
That precise point isn’t exposed directly (it’s an internal LSN), but you can think of it as “the database as it looked at some time during the backup.”
2026-01-20