Article ID: 319701
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 Q319701
BUG #: 355915 (SHILOH_BUG)
BUG #: 102086 (SQLBUG_70)
SYMPTOMS
A restore of the msdb system database leaves the database in a loading state (unrecovered) and changes in the differential and log backups are not applied if the following conditions are true:
- The restore is performed from Enterprise Manager (SEM).
- The restore operation is a combination of:
- A full database backup and a differential backup.
- A full database backup and transaction log backups.
- A full database backup and a combination of differential and transaction log backups.
NOTE: To perform transaction log backups of the msdb database in SQL Server 2000, you must change the recovery model to "bulk logged" or "full." In SQL Server 7.0, you must have the "trunc. log on chkpt." setting turned off for the msdb database.
RESOLUTION
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, restore the msdb database from Query Analyzer by using the Transact-SQL RESTORE commands instead of through SEM.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.
MORE INFORMATION
When you restore the msdb database from Enterprise Manager, it reports the following error message:
The two initial lines are expected because a restore operation inserts an entry in msdb in the restorehistory table. However, when restoring msdb, the restore operation cannot insert in this table because the msdb database is in the middle of a restore. Note that the third line in the preceding error message reports that the restore processed only one backup file, which is the initial full database backup. When you click OK you return to the Restore database dialog box, and you must click Cancel to exit from this dialog box. The problem is that the changes in the differential or the log backups are not applied to msdb and the database is left in a loading state (unrecovered). To recover the database you can run the following code in Query Analyzer:
RESTORE DATABASE msdb WITH RECOVERY
When you restore msdb from Query Analyzer, it reports errors 927 and 3009 for each restore operation except the last one. Keep in mind that all of the following restore operations, except the last, use the option WITH NORECOVERY. However, the msdb database is not left in a loading state.
The following example restores a full database backup, followed by a differential backup, and ends with a transaction log backup:
RESTORE DATABASE Msdb FROM DISK = 'C:\msdbfull.bak'
WITH NORECOVERY
GO
RESTORE DATABASE Msdb FROM DISK = 'C:\msdbdiff1.bak'
WITH NORECOVERY
GO
RESTORE LOG Msdb FROM DISK = 'C:\msdblog2.bak'
GO
The preceding commands generate the following output:
Note that the output reports that three backups were processed and that all the differential and log backups were applied.
REFERENCES
SQL Server Books Online; topic: "RESTORE"
Keywords: kbbug kbfix kbsqlserv700presp4fix KB319701
