Using this method, the inactive portion of the transaction log is retained until it is truncated, which normally occurs when it is backed up. The transaction log can then be used to stage a recovery either to a point in time or to a named transaction. The Full Recovery model provides maximum recoverability but it uses the most log space and does not provide maximum performance for bulk operations.
This method is identical to the Full Recovery model except that bulk operations are not logged and thus cannot be recovered.
Backup and recovery concepts Recovery factors
SQL Server 7.0 provides similar recovery except that functionality is defined by two attributes, these are:
Truncate Log on Checkpoint
Enables “simple” logging, that is, the inactive portion of the transaction log is truncated on checkpoint.
Select Into/Bulk Copy
Bulk operations are not logged.
Backing up the transaction log
Transaction logs can be backed up in SQL Server 2000 or SQL Server 2005 for databases which are set in either full or bulk-logged mode. In SQL Server 7.0, transaction logs can be backed up if “Truncate Log on Checkpoint” has not been selected. By default a transaction log is truncated after it has been backed up. However, it is not truncated following a full database or differential backup.
The main factors in deciding how frequently to backup a transaction log would be
Conservation of log space.
How close to the failure point you must be able to recover in case of a disk
During peak periods in a high transaction environment, it may not be unusual to back up the transaction log on an hourly basis.
Unlike the transaction log backup, the differential backup is a backup of the database. The differential includes all of the changes made since the last full backup. If you have made several differential backups since the last full backup, then to accomplish a full restore to the end of the last differential backup, you would only need to restore the last full database, followed by the last differential. You would not need to restore any of the intermediate differentials.