Microsoft KB Archive/319697

= FIX: SQL Enterprise Manager Restore to Point in Time Does Not Stop at Requested Time and the Database is Left in a Loading State =

Article ID: 319697

Article Last Modified on 10/30/2003

-

APPLIES TO


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

-



This article was previously published under Q319697



BUG #: 356493 (SHILOH_BUGS)

BUG #: 101314 (SQLBUG_70)



SYMPTOMS
If all of the following conditions are true, a restore to a point-in-time leaves the database in a loading state (unrecovered) and does not stop at the requested time:
 * You perform the restore through SQL Enterprise Manager (SEM).
 * You restore one backup file (device) at a time, and you specify a &quot;Point in Time&quot; to stop in the last transaction log you restore.



CAUSE
A restore to a point-in-time from Enterprise Manager does not stop at the requested time because Enterprise Manager automatically changes the point-in-time value to the backup time of the last transaction log.

A mechanism in the restore to a point-in-time leaves the database in a loading state if there are no log entries after the time you specify, to allow you to apply more transaction logs if you want.



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

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



WORKAROUND
To work around this behavior you can either:   Restore to a point-in-time from Query Analyzer by using the STOPAT Transact-SQL command to load the last transaction log: RESTORE LOG FROM WITH STOPAT = '2002-04-12 8:30:00' For more information about STOPAT, see the Transact-SQL &quot;RESTORE&quot; topic in SQL Server Books Online&quot;. -or-

 Restore to a point-in-time from Enterprise Manager and select all backup files in one single batch.



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 3.

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



MORE INFORMATION
In SQL Server 2000 Enterprise Manager, when you apply the last transaction log, Enterprise Manager generates an error message if the point-in-time value is less than the backup time of the last transaction log. SQL Server 2000 automatically changes the value to match the backup time of the last transaction log. Here is the error message that SEM reports:

The time specified is less than the minimum point in time allowed. Time corrected.

SQL Server 7.0 Enterprise Manager does not raise the preceding error message. However, SQL Server 7.0 does change the point-in-time value automatically to match the backup time of the last transaction log.

