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

Article translations Article translations
Article ID: 297271 - View products that this article applies to.
This article was previously published under Q297271
BUG #: 353559 (SHILOH)
BUG #: 101552 (SQLBUG_70)
Expand all | Collapse all

On This Page

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.

RESOLUTION

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.

Properties

Article ID: 297271 - Last Review: October 31, 2003 - Revision: 3.1
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbbug kbfix KB297271

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com