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

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.