May 12, 2006

MSDE to SQL Express upgrade manifests problems with msdb database during a restore

Filed under: SQL Server — marcstober @ 2:15 pm

When restoring a database to an SQL Server 2005 Express server, where that server had been upgraded from MSDE, we get errors like the following:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'mirror_count'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'is_password_protected'.
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 8081 pages in 10.138 seconds (6.529 MB/sec).

This is running the RESTORE command through SQL Server Management Studio (SSMS), or through our own utility, which is where we found the problem. We don’t get the error doing a restore through SSMS, but there’s no record of the restore in the MSDB restore history table so it’s probably just ignoring the error; in either case the database actually is restored.

Googling the error returned only this thread but a little more searching found this thread and Knowledge Base article called Changes to the readme file for SQL Server 2005 says that “During upgrade from MSDE to SQL Server Express, the msdb database is not upgraded.” This explains the problem but isn’t a solution. Apparently there’s a file called instmsdb.sql that will rebuild your msdb database, or we could just ignore the error, but neither seems like a foolproof solution. Interesting the master database is still version 80 in the upgraded-from-MSDE server. It’s not fixed in SP1 but we can hope for SP2.