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 "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:
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
Keywords: kbbug kbfix kbsqlserv2000sp3fix KB319697
