none
Which transactions will be contained in the backup RRS feed

  • Question

  • Full backup started. Afterwards, transaction T1, T2 started 

    T1 commits before full backup completes

    T2 commits after full backup completes

    Also, transaction T3 started before full backup and commits before full backup completes

    Transaction T4 started before full backup and commits after full backup completes

    Which transactions will be contained in the backup


    Kiran


    • Edited by juniorkiran Friday, January 17, 2020 12:09 PM Added
    Friday, January 17, 2020 12:03 PM

Answers

  • No. 

    In your example, T1 and T3 are committed before the backup is complete and will be included.  T2 and T4 will NOT be included after restore of the full backup because they are uncommitted when the backup finished.


    Friday, January 17, 2020 2:27 PM
    Moderator

All replies

  • A full backup contains information about the currently active transactions, see About working with SQL Server backups for more details: "Under the full recovery model or bulk-logged recovery model, conventional backups also include sequential transaction log backups (or log backups), which are required"

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, January 17, 2020 12:48 PM
    Moderator
  • So, T2 and T4 will be contained in the backup, correct?

    Kiran

    Friday, January 17, 2020 1:19 PM
  • I suggest you read this:

    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/full-database-backups-sql-server?view=sql-server-ver15

    You can re-create a whole database in one step by restoring the database from a full database backup to any location. Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. The restored database matches the state of the original database when the database backup finished, minus any uncommitted transactions.

    Friday, January 17, 2020 1:39 PM
    Moderator
  • I read the article but did not understand completely. T2 and T4 will be contained in the backup, correct?

    Kiran

    Friday, January 17, 2020 2:07 PM
  • No. 

    In your example, T1 and T3 are committed before the backup is complete and will be included.  T2 and T4 will NOT be included after restore of the full backup because they are uncommitted when the backup finished.


    Friday, January 17, 2020 2:27 PM
    Moderator
  • Thanks a lot Tom

    Kiran

    Friday, January 17, 2020 3:25 PM
  • In a way, all transactions are included in the backup. This is not to say that the other answers are wrong, though. What happens is this: first the restore restores the pages in the backup, so you will get the uncommitted state of T2 and T4. Next what happens is that T2 and T4 will be rolled back. And in the final phase T1 and T3 may be rolled forward, depending on exactly when they were comitted during the backup.

    When the database comes available, the result of T1 and T3 will be there. T2 and T4 will not.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, January 17, 2020 10:50 PM
    Moderator
  • So, Erland, T1,T2,T3 and T4 will be contained in the backup? I am confused

    Kiran

    Saturday, January 18, 2020 1:34 PM
  • So, Erland, T1,T2,T3 and T4 will be contained in the backup? I am confused

    Yes. But T2 and T4 will not be included in full. (Obviously, since they did not commit until after the backup completed.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 18, 2020 4:08 PM
    Moderator
  • Erland is technically correct. 

    I was trying not to confuse the OP with the technical details of the uncommitted transactions actually being physically in the full backup file, but rolled back during the restore becuase they are uncommitted.

    It depends on what the OP means by "Which transactions will be contained in the backup"?
    Tuesday, January 21, 2020 12:49 PM
    Moderator