msdb error

Hi

Need help with a duff msdb database in SQL7. The error -2147467259 comes up when trying to connect to msdb. Also, the message in brackets in the database view reads (Loading\Suspect) next to msdb, which is greyed out - there are also "no items" listed under the database in this view.

Having taken on the role of I.T.Manager, there have been no backups of the msdb database and a bit of a hotchpotch system for backing up the critical data was done via Winzip. I have created a batchfile that schedules a backup of the data on a nightly basis BUT can not restore a good msdb due to the fact there has never been a backup made (!!).

I have considered running the scripts from the instal CD, but am not sure that this will overwrite the dud successfully.

I am not highly skilled in the under the hood workings of SQL7 and would appreciate any advice from my peers

Thanks

KevQ1 I am not highly skilled in the under the hood workings of SQL7 and would appreciate any advice from my peers

(It has been a few years since I delt with this topic in pre Sql Server 2k environments; but for the most part, I think the following is valid for 7.0 also.)

A1 'Loading' may indicate that an MSDB restore was performed but not recovered following completion. Have you simply tried to recover i.e.(RESTORE DATABASE msdb WITH RECOVERY)?

A2 If that doesn't work, creating MSDB with install scripts is going to be involved, (not simple, but should be 'possible'). You will likely find it easier to: Install Sql Server elsewhere and backup MSDB from there (to disk). Then, use that MSDB backup dump to restore over your MSDB database.

A3 Restoring over the MSDB database is sometimes not possible (due to problems in Master), in which case you'll want to restore master and then try restoring MSDB again.

A4 If A3 doesn't allow a sucessful restoring over the MSDB database; you probably need to use rebuildm.exe (C:\Program Files\Microsoft SQL Server\80\Tools\Binn) which will fix master. Then run all your login scripts, restore / reattach your user DBs etc., etc., as necessary.