In the simple model the transaction log automatically truncates itself when the data file has reached certain checkpoints. The benefit is that the log is kept small, but it does so at a cost. Database restores can only occur to the exact point that the last full backup was taken. With a simple recovery model, there’s no opportunity to do a point-in-time restore.
In the bulk-logged recovery model, bulk operations are minimally logged but the transaction log does maintain a full record of other transactions.
And finally the full recovery model keeps a complete transaction log of modifications to the database.With this model point-in-time restores of the database are possible, where the database can essentially be rolled back to an arbitrary point. This is possible since during the restore, the database is essentially reconstructed one step at a time based on the sequence of actions performed in the log. You simply need to tell SQL Server when to stop processing the log.
Sources:
Comments