What is TUF File In SQL Server

         TUF file is a Microsoft SQL Server Transaction Undo file. .TUF File contains the information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed. A transaction undo (.TUF) file is required if a database is loaded in read-only state. In this state, further transaction log backups may be applied.  

            This file plays an important role in Standby mode… the reason being very obvious while restoring the log backup all uncommitted transactions are recorded to the undo file with only committed transactions written to disk which enables the users to read the database. So when we restore next transaction log backup; SQL server will fetch all the uncommitted transactions from undo file and check with the new transaction log backup whether committed or not.

• TUF file is known as Transaction Undo File.
• This file is created when log shipping is configured in SQL server in standby mode.
• This file consists of list of uncommitted transactions which were pushed with t-log backup of primary server.
• If this file is deleted we either have to restore secondary database from full\differential or reconfigure log-shipping
• This file is located where transaction log file exists. You can also keep it in different folder while restoring database in standby mode.

Example:

Restoring database in stand by mode requires TUF FILE as below:

Restoring full backup:

RESTORE DATABASE [Ship] FROM  DISK = N'\\WIN7-PC\Logshipping_S\Ship_Fullbackup.bak'

WITH STANDBY = N'D:\ROLLBACK_UNDO_Ship.BAK', STATS = 10

Restoring Differential backup:

RESTORE DATABASE [Ship] FROM  DISK = N'\\WIN7-PC\Logshipping_S\Ship_Diffbackup.bak'

WITH STANDBY = N'D:\ROLLBACK_UNDO_Ship.BAK', STATS = 10

Restoring T-Log backup:

RESTORE LOG [Ship] FROM  DISK = N'\\WIN7-PC\Logshipping_S\Ship_logbackup.bak'

WITH STANDBY = N'D:\ROLLBACK_UNDO_Ship.BAK', STATS = 10

Yes we can restore any backup using TUF file to move database in standby restoring mode