You can certainly try. Before you try, just make sure you make at least two backups of all the databases you cannot afford to lose. then play around. i had an msdb backup i couldn't restore, and i tried so many different solutions. eventually i did what you are asking below. it worked, but i of course had to recreate all my jobs, etc.


------------
Russell at 6/14/01 2:51:02 PM

Kurt - Thanks for the Infromation and Details.

I needed some more information :

If I rebuild the Master DB, will it configure all the System Databases - Master, Model and MSDB Databases to a fresh build? I mean, along with all the SQL Server Agent Data (Jobs, replication etc.)on MSDB won't I also lose all the information in Master and Model too? Can I just re-build the Master & Model to the Current state, using the Backup's; After running the Rebuildm.exe?.....will that hose the system?

In order to save the Master and Model Databases, I was also wondering whether it's possible to backup a copy of ONLY the MSDB databse from a Fresh SQL server 2000 Build on another server; and Re-build the MSDB database on the Corrupted server, using the backup from the Fresh Build? Will that hose the other system databases on the server because this Backup copy would be from a different SQL 2000 Install?.....HAS ANYONE TRIED this? ANY ADVICE/WARNINGS?

Thanks

Russell




------------
Kurt at 6/13/01 8:50:28 PM

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