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

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

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:

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.

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.

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.