Share via

Point in time restore after initial backup

Duygu Hasan 20 Reputation points
2026-03-06T12:46:29.77+00:00

Hello,
I have a specific question to which i coulnd't really find an exact answer. I have a scenario in which let's say I have an initial full backup(no backups present before that) that for some reason is slow and takes let's say 5 hours, starting at 10:00 and ends successfully at 15:00. And let's say I have transaction log backup created at 15:10. Will the transaction log include the transactions that happened from 10:00 to 15:10, or it will include only the ones from 15:00 to 15:10. As of my understanding the full backup is for 10:00 and restore with that backup will result in restoring to 10:00 exactly, is there a way for me to restore to let's say 12:30 then? As of my understanding I should be able to recover to that time with the first transaction log backup that was created after the initial backup.

Thanks,
Duygu

SQL Server | SQL Server Transact-SQL
0 comments
Answer accepted by question author
  1. Erland Sommarskog 133.1K Reputation points MVP Volunteer Moderator
    2026-03-06T21:37:50.8566667+00:00

    The full backup includes enough of the transaction log, so that when you restore the database, you will get the state at 15:00. That is, say that the pages for the Orders table were written to the backup at 12:00. When the database is restored, RESTORE will read this pages from the backup and write to the database. It will then apply the changes in the transaction log that is in included in the back, so that you get all changes at 15:00.

    You cannot restore the database to an point in time before the full backup completed. (Or more precisely, to do that, you would need an older backup, but your presumption was that you did not have one.)

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2026-03-06T12:46:50.44+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    The transaction log backup at 15:10 will contain all log records that were generated from the time of the previous log backup (or from when the database entered full recovery) up to 15:10, regardless of how long the full backup ran.

    Key points for the scenario:

    1. Full backup time vs. log contents
      • A full database backup does not truncate the log and does not define the start of the log chain.
      • The log chain is continuous and independent of data (full/differential) backups. The example in the documentation shows this explicitly: a log backup taken at 8:00 PM contains log records from 4:00 PM–8:00 PM, even though a full backup was taken at 6:00 PM in the middle of that interval.
      • Therefore, a log backup taken at 15:10 will include all committed and uncommitted log records from the end of the previous log backup up to 15:10, including those generated between 10:00 and 15:00 while the full backup was running.
    2. What point in time a full backup represents
      • A full backup contains a transactionally consistent view of the database as of the end of the backup, not the start.
      • In practice, when restoring, the full backup is treated as a base, and then log backups are applied to roll forward to the desired recovery point.
    3. Restoring to 12:30 in this scenario
      To restore to 12:30 (assuming full recovery model and a valid log chain):
      1. Restore the full database backup with NORECOVERY. This full backup can be older than the target time, as long as all subsequent log backups are available.
      2. Restore the log backup that contains 12:30 using WITH STOPAT: 
              RESTORE LOG YourDatabase
      FROM YourLogBackupDevice
      WITH STOPAT = '2024-01-01T12:30:00',
           RECOVERY;
      • The documentation states that for a point-in-time restore, a full backup whose end point is earlier than the target time is restored, and then every subsequent log backup up to and including the one that contains the target time is applied with STOPAT.
    4. How the log chain works across full backups
      • The log backup sequence is independent of full backups; full backups do not break the log chain.
      • As shown in the example sequence (8:00 full, noon log, 4:00 log, 6:00 full, 8:00 log), the 8:00 PM log backup spans 4:00–8:00 PM, including the time of the 6:00 PM full backup.
      • Similarly, in the described scenario, the 15:10 log backup spans the time during which the full backup was running.

    So, yes: using the first log backup taken after the initial full backup (and any earlier log backups if they exist), it is possible to restore to an intermediate time such as 12:30, by restoring the full backup and then applying the log backup with STOPAT to that time.

    References:

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.