Microsoft KB Archive/174267
Article ID: 174267
Article Last Modified on 10/3/2003
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q174267
If users are dumping the database occasionally and subsequently dumping the transaction log, the following error will be generated during the restore process when attempting to load the first transaction log in the sequence:
Note that if users are dumping the MSDB database and only restoring the database, this problem will not occur.
This problem is caused by the insertion of a new record in the MSDB SysRestoreHistory table immediately after the database load. This causes the timestamp in the database to be updated to a value greater than the values in the transaction log dumps, and generates the error message.
To work around this problem, perform the following steps:
Set the 'Read only' option on for the MSDB database to True, either by using the SQL Enterprise Manager, or from ISQL/w, as in the following example:
exec sp_dboption <dbname>, 'read only', true go
- Load the MSDB database. The 'Read only' option will not allow the restore process to insert a new entry into the SysRestoreHistory table, and will leave the timestamp at the same value as when the database was dumped. At the end of the restore process, the following error message will be generated, indicating that the restore history record could not be inserted because the database is 'Read only': Note that the last part of the error message indicates that the dump or load was still successful.
- Load any transaction logs for MSDB in sequential order.
- After all of the transaction log dumps have been loaded, clear the 'Read only' option on the database.
As part of the dump and load facility in Microsoft SQL Server 6.5, four new tables were added to track dump and load history for each of the databases. SysBackupHistory, SysRestoreHistory, SysBackupDetail, and SysRestoreDetail were added to the system tables in the MSDB database; the information they contain is displayed in the Database Backup/Restore window of SQL Enterprise Manager. Each time a database is dumped or loaded, a new record is inserted into the appropriate table.
For more information on setting database options, see the SQL Server Books Online.
Keywords: kberrmsg kbprb KB174267