Introducing Arcserve Backup Agents and Options › Using Agents › Agent for Microsoft SQL Server › Backup and Recovery Best Practices › Backup Concepts › Backup Considerations-MSSQLSvrW › Transaction Log Backups

Transaction Log Backups

Transaction logs record Microsoft SQL Server database activity. When using the Full or Bulk-Logged Recovery Models, they should be backed up frequently. To back them up, run self-contained Transaction log backups separately from database backups. Transaction log backups offer the following advantages over other types of backups:

• Generally faster than differential backups
• Typically faster and smaller than full database backups (unless they have not been truncated recently)
• Typically have less impact on database performance while running
• Normally able to be restored to a specified point in time, rather than only to the time the backup was made

After you have modified the structure or configuration of the database (for example, by adding more data or log files, or changing the recovery model), you must perform a full database backup before performing a differential or Transaction log backup. For more information, refer to Required Full Backup.

It is sometimes possible to perform a Transaction Log backup of a damaged database. If the database is in the Suspect or Damaged state, and its transaction log files are intact, you will be able to perform a Transaction Log backup without Truncation. This will allow you to recover the database to the point in time immediately before the failure.

Important! Transaction logs are not backed up during full or differential database backups. You must back them up by running separate Transaction log backups or using the Backup Transaction Log After Database option. Transaction logs are only Truncated as part of a Transaction Log backup. If they are not backed up and truncated, they may grow until the disk is full. If this happens, you must perform a Transaction Log backup With Truncation, and then shrink the Transaction Log files to reclaim disk space. In SQL 2005 and later, multiple log backups with Truncation may be needed before log files can be significantly shrunk.

Note: Databases which use the Simple Recovery Model do not permit or require Transaction Log backups. Microsoft SQL Server manages the maintenance of the Transaction Logs for these databases automatically.

This section contains the following topics:

• Truncate Transaction Logs