MSDB Can only be restored from a backup...and that must be a backup made from SQL 2000 (not previous versions). If you have no backups of MSDB you're in bad shape especially if you have jobs or replication set up. If you don't have a backup you can rebuild MSDB by rebuilding the Master DB. To do that I'd back up Master and Model (make 2 copies or each just for safety). I'd also back up all your user databases just in case. Then you need to rebuild the master by shutting down All SQL Server services and running Rebuildm.exe. Restart SQL, and restore the master. You'll have lost everything in MSDB that you put into it but at least it will have been rebuilt.

As part of your backup plan, make sure you always backup Master, MDDB, Model and any user databases. The system databases do (rarely) get hosed.





------------
Russell at 6/13/01 7:17:06 PM

Hi,

I am having problems with my SQL server Agent.

1) When I try to create a New Job/Alert/Operator, I get an error message:

Error 207: Invalid Column Name "category_id"
Invalid Column Name "netsend_address"
Invalid Column Name "last_netsend_date"
Invalid Column Name "last_netsend_time"

.....All these are referring to columns in System Tables of the system Database MSDB.

2)In another SQL Server...say "A", in it's SQL Server Agent, using the Multi-Server Administration, I set up the SQL Server Agent of "A" as the Master (MSX) server, and set up a Target(TSX) server pointing to the Server "B" that has problems in SQL Agent....referred in 1) above. When I try to "Detach the MSX Server" from server B, i get a message Error 207: Invalid Column Name "netsend_address", and cannot detach..........This is again referrring to a System Table.

It appears that the problem is with the MSDB database being corrupt. Is there a way I can Repair a Corrupt MSDB without Re-installing SQL Server 2000 and without a Backup.

Any help greatly appreciate.

Thanks

Russell