FIX: Unable to Restore to a Point in Time in First Transaction Log Backup

Article translations Article translations
Article ID: 260182 - View products that this article applies to.
This article was previously published under Q260182
BUG #: 235804 (SHILOH_BUGS)
BUG #: 57677 (SQLBUG_70)
Expand all | Collapse all

On This Page

SYMPTOMS

You cannot restore to a point in time within the first transaction log backup for a database when you use SQL Server Enterprise Manager to perform the RESTORE operation.

In SQL Server 7.0, despite the value entered by the user, the GUI always defaults to the time at the end of the first transaction log.

In SQL Server 2000, the following error message occurs if you enter a point in time within the first transaction log:
The time specified is less than the minimum point in time allowed. Time corrected.
After the user clicks OK to close the message box, you find that the end time of the transaction log has replaced the time entered earlier.

The subsequent execution of the RESTORE command restores the database to the end of first transaction log backup.

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

Use the RESTORE LOG statement in SQL Server Query Analyzer with the STOPAT parameter to restore the database to a point in time within the first transaction log backup.

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

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

MORE INFORMATION

For more detailed information on how to use Transact-SQL statements to perform a restore operation, refer to the "How to restore to a point in time (Transact-SQL)" topic in SQL Server Books Online.

Steps to Reproduce the Problem

  1. Perform a full database backup and at least one transaction log backup that contains multiple transactions on a database. To see the behavior, make sure that the transaction log backup occurs at least 2 minutes after the database backup.
  2. In SQL Server Enterprise Manager, open the Restore Database dialog box. Select the full database backup and first transaction log backup for the RESTORE operation. Specify a point in time within the first log backup.

    In SQL Server 7.0, the GUI overwrites the value entered with the end time of first log backup.

    In SQL Server 2000, the error message described in the "Symptoms" section occurs first. Also note that the end time for the first log backup replaced the specified time in the GUI after you click OK to close the message box. Execution of the RESTORE command restores to the end of first transaction log backup.

Properties

Article ID: 260182 - Last Review: October 31, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbbackup kbbug kbfix KB260182

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