Transaction log backup vs tuf file in log shipping?
Hi, I just want to know one thing, If we take a t-log backup, Is it backing up the Uncommited transactions as well? What is the use of .tuf file in standby mode and what's difference between normal transaction log backup in logshipping with norecovery mode and log backup in stand by mode in log shipping?
The log backup does include uncommitted transactions. It rolls these back during recovery to make the restored database transactionally consistent. With the two types of log shipping restores you mention, there is no difference in transaction log backups - the difference is the state in which the restore leaves the database. With standby mode, the uncommitted transactions are rolled back to make the database transactionally consistent so it can be read. The transaction log records to reopen these transactions are stored in the undo file. When a subsequent log backup is restored, these transactions are replayed to their open state prior to the restore. With norecovery mode, the undo phase of recovery is skipped (You can read about the three phases of recovery here - [
https://technet.microsoft.com/en-us/library/ms191455(v=sql.105).aspx]). Uncommitted transactions are left uncommitted and the database remains in a transactionally inconsistent state. Subsequent log backups can be restored without reopening these transactions, so no undo file is required. Books online has a good high-level overview of log restores. Steps 10 and 11 touch on this topic - [