Any changes required to be made to the database are written
to the transaction log, for example any insert,update,delete done
on the database are logged first in the transaction log file. When
transaction is committed (explicit/implicit) the data is marked to be
written to the database.
Two types of transaction
Explicit = You specify where to start the tran and when to end it
Implicit = We dont specify begin and end tran
From Recovery Models point of view
1> Full Recovery : Every transaction made to the database
is first logged to the log file
2> Bulk Logged : When there is bulk operation is minimally logged
3> Simple : Completed transaction log are deleted from log file
SELECT name,cmptlevel,DATABASEPROPERTYEX(name,'Recovery')AS RecoveryModel, DATABASEPROPERTYEX(name,'Status') as Status FROM sysdatabases.
Above will give the list of database and the configured recover model of each.