The purpose of Transaction Log

We all know that all DDL and DML changes are logged in Transaction log. But when I ask a question that what is the primary purpose of Transaction log in SQL Server then the most common answers that I get are following:

·         For Transaction log backup.

·         For Transactional Replication, Log shipping or Mirroring.

As we know these two features can not be implemented in SIMPLE recovery model but transaction log is still maintained in this recovery model. This indicates that even these features use transaction log but these are not the prime purpose of Transaction log.

The primary purpose is Rollback & Recovery.

We all know that reversing the changes is called Rollback in SQL Server. This is the process to keep the database consistent and integrated. Transaction is the unit of action and either all statements of a transactions complete successfully or none.  

So what is Recovery? Recovery includes two steps: Redo and Undo. In Redo all changes of completed transactions, after the last checkpoint are updated in data pages. In Undo process all changes of uncompleted transactions up to last checkpoint are rolled back.

Recovery is performed on a database in two cases:

1.       While starting the database when SQL Server starts

2.       While restoring the database.

The recovery process while restoring a database can be paused for later time by using NORECOVERY option in RESTORE DATABASE or RESTORE LOG command.