SQL Transactions: When do I Roll Back?
In other words, does one ever roll back manually?


No not as such, unless someone is doing testing I have hardly seen people manually doing a rollback. The rollback is specified in stored procedures or transactions in which you either want complete change to be entered or nothing at all.


  As far as I know, the transaction will be automatically rolled back if one of the SQL statements fails, such as an invalid INSERT or UPDATE.


Depending on how transaction is initiated, if it is under explicit begin transaction and commit yes it will rollback completely to its initial state after the query fails, while if the transaction is not under begin transaction and commit it will store the changes done till the query failed and from after the query failed the it would be stopped from making further changes.

A classic case where system initiates a rollback is when a deadlock happens and SQL Server by internal mechanism chooses deadlock victim, kills the transaction and it may or may not rollback depending on whether it was explicit or implicit transaction. Like when normal select statement is killed their is no rollback as such.