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

View products that this article applies to.

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.

Back to the top

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 (http://support.microsoft.com/kb/290211/EN-US/ ) 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.

Back to the top

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 (http://support.microsoft.com/kb/301511/EN-US/ ) 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

Back to the top

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.

Back to the top

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.

Back to the top