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

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.

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:

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:

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.

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.

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

For additional information, click the article number below to view the article in the Microsoft Knowledge Base: