FIX: SQL Enterprise Manager RESTORE Msdb Leaves Database in Loading State and Does Not Apply Differential or Log Backups

View products that this article applies to.

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:
1. A full database backup and a differential backup.
2. A full database backup and transaction log backups.
3. 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.

Back to the top

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 (http://support.microsoft.com/kb/301511/EN-US/ ) INF: How to Obtain the Latest SQL Server 7.0 Service Pack

Back to the top

WORKAROUND

To work around this behavior, restore the msdb database from Query Analyzer by using the Transact-SQL RESTORE commands instead of through SEM.

Back to the top

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.

Back to the top

MORE INFORMATION

When you restore the msdb database from Enterprise Manager, it reports the following error message:

Database 'msdb' cannot be opened. It is in the middle of a restore
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1217 pages in 2.268 seconds (4.392 MB/sec).

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:

Processed 1216 pages for database 'Msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'Msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
Backup or restore operation successfully processed 1217 pages in 3.776 seconds (2.638 MB/sec).
Processed 984 pages for database 'Msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'Msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
Backup or restore operation successfully processed 985 pages in 1.762 seconds (4.575 MB/sec).
Processed 2 pages for database 'Msdb', file 'MSDBLog' on file 1.
Backup or restore operation successfully processed 2 pages in 0.004 seconds (3.712 MB/sec).

Note that the output reports that three backups were processed and that all the differential and log backups were applied.

Back to the top