Microsoft KB Archive/272093

= Description of the effects of nonlogged and minimally logged operations on transaction log backup and the restore process in SQL Server =

Article ID: 272093

Article Last Modified on 12/7/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



This article was previously published under Q272093



SUMMARY
Some operations, which are either nonlogged, minimally logged or that remove the inactive portion of transaction log, may have an effect on the sequence of the transaction log. This article provides information about these operations and their effect on the subsequent transaction log backup and restore process.

Compared to SQL Server 7.0, SQL Server 2000 uses three new recovery models. The difference in SQL Server 2000 is also described in this article.



SQL Server 7.0
Depending on the nonlogged operation, SQL Server 7.0 may behave in three different ways when attempting to perform a transaction log backup after such nonlogged operations:  Behavior One The backup fails with these error messages:

Server: Msg 4213, Level 16, State 1, Line 1

Cannot allow BACKUP LOG because file dbname has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.

-and-

Server: Msg 3013, Level 16, State 1, Line 1

Backup or restore operation terminating abnormally.

What Causes This Behavior?

Nonlogged operations that lead to Behavior One include:

 Nonlogged bcp SELECT INTO WRITETEXT UPDATETEXT Data Transformation Services (DTS) with Use Fast Load and Table Lock enabled.</ul>

All of the preceding operations involve either modifying data or inserting new data. Because the new data is not fully logged in the transaction log, the log can not be used to restore data later in the case of a catastrophic failure. A full or differential database backup must be performed before transaction log backups can resume.

</li> The backup fails with these error messages: Behavior Two The backup returns this warning message, and then performs the transaction log backup:

There is no current database backup. This log backup cannot be used to roll forward a preceding database backup.

What Causes This Behavior?

Operations that lead to Behavior Two include:

<ul> BACKUP LOG WITH TRUNCATE_ONLY</li> sp_dboption &quot;trunc. log on chkpt&quot;, set to TRUE, followed by

sp_dboption &quot;trunc. log on chkpt, set to FALSE.</li></ul>

These operations truncate the inactive portion of transaction log without backing up the transaction log. After truncating the log, subsequent attempts to backup the transaction log return the warning message. SQL Server does continue to create the transaction log backup. However, this transaction log backup is invalid and can not be restored. The following error messages are returned when attempting to restore such log backups:

Server: Msg 4305, Level 16, State 1, Line 1

This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log.

-and-

Server: Msg 3013, Level 16, State 1, Line 1

Backup or restore operation terminating abnormally.

This behavior is by design. Any user who executes either of the preceding operations should be aware of the nature of the operations and the consequence. A full or differential database backup should be performed if any of the preceding operations are performed.</li> The backup fails with these error messages: Behavior Three The transaction log is backed up without any warning message.</li></ul>

What Causes This Behavior?

Operation that leads to Behavior Three:
 * TRUNCATE TABLE

Truncate table reclaims the pages used by the table. The transaction log records the page deallocation information. Therefore, the operation can be rolled forward. The subsequent transaction log backup is allowed and can be used for a later restore.

SQL Server 2000 and in SQL Server 2005
In the full recovery model, every change to the database is logged. So, none of the preceding error conditions occur.

In the simple recovery model, any attempt to backup the transaction log always fails with the following error messages:

Server: Msg 4208, Level 16, State 1, Line 1

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

-and-

Server: Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

In the Bulk-logged model, minimal logging may be performed for the following operations to save transaction log space:
 * Bulk load operations, including bcp and BULK INSERT.
 * CREATE INDEX, including indexed views.
 * SELECT INTO
 * WRITETEXT
 * UPDATETEXT
 * DTS with Use Fast Load and Table Lock enabled.

Different from SQL Server 7.0, SQL Server 2000, and SQL Server 2005 actually allows the transaction log to be backed up after such minimally logged operations. The backup of such transaction logs not only backs up the log, it also backs up the extents allocated to the preceding operations. Therefore, such log backups can be used for a later restore. However, the Bulk-Logged Recovery model only allows the database to be restored to the end of a transaction log backup when the log backup contains bulk changes. Point-in-time recovery is not supported. Moreover, backing up a log that contains bulk-logged operations requires access to all the data files in the database. If the data files are not accessible, the final transaction log cannot be backed up and all committed operations in that log are lost.

In SQL Server 2000 and in SQL Server 2005, the truncate log on checkpoint option exists only for backward compatibility. Microsoft recommends that you use the simple recovery model instead. If you change the recovery model from full or bulk-logged to simple and then switch back, subsequent transaction log backups return the same warning message as described in Behavior Two of the &quot;SQL Server 7.0&quot; section and perform the backup of the log after the warning message.

Subsequent restore operations using such transaction log backups fail with the following error messages:

Server: Msg 4305, Level 16, State 1, Line 1

The log in this backup set begins at LSN LSNnumber, which is too late to apply to the database. An earlier log backup that includes LSN LSNnumber can be restored.

-and-

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Behavior Three described in the &quot;SQL Server 7.0&quot; section also applies to SQL Server 2000.

Keywords: kbinfo KB272093

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.