Backup Concepts
120 Agent for Microsoft SQL Server Guide
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
(see
page 116).
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:
■
Restore Requirements for Transaction Log Backups
■
Truncate Transaction Logs
(see page 121)
