Microsoft KB Archive/272683

= Restore of a database with an incorrect named transaction may leave the database in the loading status in SQL Server 2000 =

Article ID: 272683

Article Last Modified on 2/22/2007

-

APPLIES TO


 * 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 Q272683



SYMPTOMS
Microsoft SQL Server 2000 supports a new feature that allows users to insert a named transaction in the transaction log and allow recovery to that point, similar to point-in-time recovery as implemented in SQL Server 7.0. In addition, these marks are tracked in the msdb database, enabling users to determine what marks are available and plan recovery accordingly.

When you use RESTORE DATABASE WITH STOPATMARK or STOPBEFOREMARK to restore to a named transaction, RESTORE remains in loading status if you specify an incorrect named transaction.

This results in the following error message:

This log file contains records logged before the designated mark. The database is being left in load state so you can apply another log file.

Microsoft SQL Server 2005
In SQL Server 2005, you receive the following error message. The database is marked in the Restoring state in SQL Server Management.Studio.

This log file contains records logged before the designated mark. The database is being left in the Restoring state so you can apply another log file.



CAUSE
If SQL Server fails to find the specified mark name, the STOPATMARK or STOPBEFOREMARK request is ignored so that more transaction log backups can be applied with the correct mark name.



WORKAROUND
To get the database out of loading status (without restoring another transaction log backup), run the following statement: RESTORE DATABASE DatabaseName WITH RECOVERY where DatabaseName is the name of the database left in load.