Microsoft KB Archive/297271

= FIX: Point in Time Recovery May Recover Database if the Transaction Log That is Applied Does Not Contain Any Transactions =

Article ID: 297271

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q297271



BUG #: 353559 (SHILOH)

BUG #: 101552 (SQLBUG_70)



SYMPTOMS
Under the conditions that follow, the restoration of a transaction log with the use of one of the point in time options (STOPAT, STOPBEFOREMARK, STOPATMARK) recovers the database instead of leaving it in a non-recovered state that allows subsequent transaction logs to be restored:
 * The point in time is beyond the latest date in the transaction log that is being restored or the mark does not exist in the transaction log that is being restored.NOTE: The second condition applies to SQL Server 2000 only.


 * The transaction log that is being restored does not contain any transactions.



SQL Server 2000
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2.

The English version of this fix for SQL Server 2000 should have the following file attributes or later:   File name     Platform

s80288i.exe  x86 - SQL Server 2000 original release fix s80404i.exe  x86 - SQL Server Service Pack 1 fix

NOTE: Because of file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

SQL Server 7.0
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack

NOTE: The following hotfix was created prior to Microsoft SQL Server 7.0 Service Pack 4.

The English version of this fix for SQL Server 7.0 should have the following file attributes or later:   File name     Platform --

s70991i.exe  x86



WORKAROUND
To work around this behavior, use either of the methods that follow:
 * Only use the point in time recovery options STOPAT, STOPBEFOREMARK or STOPATMARK when you restore a transaction log that contains the time or mark specified.
 * Do not restore transaction logs that do not have any transactions and continue to restore with subsequent transaction logs. You can identify a transaction log that does not have any transactions by examining the transaction backups with the LOAD HEADERONLY statement and checking to see if the FirstLSN and LastLSN columns have the same value.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

SQL Server 2000

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

SQL Server 7.0

This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.

Keywords: kbbug kbfix KB297271

-

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

© Microsoft Corporation. All rights reserved.