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 translations Article translations
Article ID: 319697 - View products that this article applies to.
This article was previously published under Q319697
BUG #: 356493 (SHILOH_BUGS)
BUG #: 101314 (SQLBUG_70)
Expand all | Collapse all

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 "Point in Time" 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.

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

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 <dbname> FROM <backup device> 
    	WITH STOPAT = '2002-04-12 8:30:00'
          
    						
    For more information about STOPAT, see the Transact-SQL "RESTORE" topic in SQL Server Books Online". -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.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
260235 BUG: Point-in-Time Recovery Adds Incorrect Seconds Value to Recovery Time Selected in SEM
297271 FIX: Point in Time Recovery May Recover Database if the Transaction Log That is Applied Does Not Contain Any Transactions
260182 FIX: Unable to Restore to a Point in Time in First Transaction Log Backup

Properties

Article ID: 319697 - Last Review: October 30, 2003 - Revision: 4.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbsqlserv2000sp3fix kbbug kbfix KB319697

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