Log Backups Paused for Good Reason

Log backups are important to recovery, so why does SQL Server pause them during a full database backup?

Kimberly L. Tripp

August 20, 2002

2 Min Read
ITPro Today logo in a gray background | ITPro Today

Log backups are an important part of a full database backup. SQL Server backs up the transaction log as part of a full backup to make the full backup transactionally consistent when the full backup is restored. Before you can back it up, the transaction log needs to be accessible and complete: SQL Server must have access to all log activity that has occurred throughout the full backup, and the log can't have been cleared. However, the default transaction log backup behavior is to back up and then clear what was backed up. If log backups were allowed during a full backup, the full backup couldn't back up the log and thus would be transactionally consistent during a restore.

For example, let's say you start a full backup at 2 A.M. and it completes at 4:47 A.M. In SQL Server releases before 7.0, the restored image reflects the database at 2 A.M., whereas in the later releases, the restored image reflects the committed transactions as of 4:47. The only way to get all the information is to ensure that the activity that occurred during this time is backed up and stored with the full backup.

During a restore, the log information is replayed to make the full database backup transactionally consistent with the state of the database as it was at the time the backup completed. However, although the full backup includes a portion of the log, it doesn't clear the transaction log. Transaction log backups are the only action that should ever clear the transaction log. As of SQL Server 7.0, you have no reason to manually clear the transaction log in a properly maintained database. You can include the two statements

BACKUP LOG dbname WITH NO_LOG 

and

BACKUP LOG dbname WITH TRUNCATE_ONLY 

if you want to manually clear the log, but you don't need to. If the transaction log becomes full, a regular transaction log backup will clear whatever was backed up. Regular, consistent transaction log backups will help keep the transaction log small and give you the lowest risk of lost data.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like