Article
10/04/2012

Creating Transaction Log Backups

This topic is relevant only for databases that are using the full or bulk-logged recovery models.

This topic describes considerations for backing up the transaction log and provides links to procedures for creating log backups. For more information about transaction log backups, see Working with Transaction Log Backups.

Conditions for Backing Up the Transaction Log

Minimally, you must have at least one full backup before you can make any log backups. After that, the transaction log can be backed up during any backup, except another log backup. We recommend that you take log backups frequently, both to minimize work loss exposure and to enable log truncation.

Typically, before you restore a database, you should try to back up the tail of the log. For information about how to create a tail-log backup and about conditions under which a tail-log backup is not required, see Tail-Log Backups.

How a Sequence of Log Backups Works

A database administrator typically creates a full database backup at a periodic interval, such as weekly. Optionally, an administrator creates a differential backup at a shorter interval, such as daily; and creates a transaction log backup frequently, such as every 10 minutes. The optimal interval between backups depends on factors such as the importance of the data, the size of the database, and the workload of the server.

If a transaction log is damaged, work that is performed since the most recent log backup is lost. This highlights the importance of putting the log files on fault-tolerant storage.

The sequence of transaction log backups is independent of the full database backups. You make one sequence of transaction log backups, and then make periodic full database backups that are used to start a restore operation. For example, assume the following sequence of events.

Time	Event
8:00 A.M.	Back up database.
Noon	Back up transaction log.
4:00 P.M.	Back up transaction log.
6:00 P.M.	Back up database.
8:00 P.M.	Back up transaction log.

The transaction log backup created at 8:00 P.M. contains transaction log records from 4:00 P.M. through 8:00 P.M., spanning the time when the full database backup was created at 6:00 P.M. The sequence of transaction log backups is continuous from the initial full database backup created at 8:00 A.M. to the last transaction log backup created at 8:00 P.M.

For information about how to apply these transaction logs, see the example in Applying Transaction Log Backups.

Note

By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).