# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Repairing Corrupt MSDB file in SQL Server 2000.

## Russell

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 &#34;category_id&#34;
Invalid Column Name &#34;netsend_address&#34;
Invalid Column Name &#34;last_netsend_date&#34;
Invalid Column Name &#34;last_netsend_time&#34;

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

2)In another SQL Server...say &#34;A&#34;, in it&#39;s SQL Server Agent, using the Multi-Server Administration, I set up the SQL Server Agent of &#34;A&#34; as the Master (MSX) server, and set up a Target(TSX) server pointing to the Server &#34;B&#34; that has problems in SQL Agent....referred in 1) above. When I try to &#34;Detach the MSX Server&#34; from server B, i get a message Error 207: Invalid Column Name &#34;netsend_address&#34;, 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

----------


## Kurt

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&#39;re in bad shape especially if you have jobs or replication set up.  If you don&#39;t have a backup you can rebuild MSDB by rebuilding the Master DB.  To do that I&#39;d back up Master and Model (make 2 copies or each just for safety).  I&#39;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&#39;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 &#34;category_id&#34;
Invalid Column Name &#34;netsend_address&#34;
Invalid Column Name &#34;last_netsend_date&#34;
Invalid Column Name &#34;last_netsend_time&#34;

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

2)In another SQL Server...say &#34;A&#34;, in it&#39;s SQL Server Agent, using the Multi-Server Administration, I set up the SQL Server Agent of &#34;A&#34; as the Master (MSX) server, and set up a Target(TSX) server pointing to the Server &#34;B&#34; that has problems in SQL Agent....referred in 1) above. When I try to &#34;Detach the MSX Server&#34; from server B, i get a message Error 207: Invalid Column Name &#34;netsend_address&#34;, 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

----------


## Russell

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&#39;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&#39;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&#39;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&#39;re in bad shape especially if you have jobs or replication set up.  If you don&#39;t have a backup you can rebuild MSDB by rebuilding the Master DB.  To do that I&#39;d back up Master and Model (make 2 copies or each just for safety).  I&#39;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&#39;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 &#34;category_id&#34;
Invalid Column Name &#34;netsend_address&#34;
Invalid Column Name &#34;last_netsend_date&#34;
Invalid Column Name &#34;last_netsend_time&#34;

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

2)In another SQL Server...say &#34;A&#34;, in it&#39;s SQL Server Agent, using the Multi-Server Administration, I set up the SQL Server Agent of &#34;A&#34; as the Master (MSX) server, and set up a Target(TSX) server pointing to the Server &#34;B&#34; that has problems in SQL Agent....referred in 1) above. When I try to &#34;Detach the MSX Server&#34; from server B, i get a message Error 207: Invalid Column Name &#34;netsend_address&#34;, 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

----------


## ec

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&#39;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&#39;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&#39;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&#39;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&#39;re in bad shape especially if you have jobs or replication set up.  If you don&#39;t have a backup you can rebuild MSDB by rebuilding the Master DB.  To do that I&#39;d back up Master and Model (make 2 copies or each just for safety).  I&#39;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&#39;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 &#34;category_id&#34;
Invalid Column Name &#34;netsend_address&#34;
Invalid Column Name &#34;last_netsend_date&#34;
Invalid Column Name &#34;last_netsend_time&#34;

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

2)In another SQL Server...say &#34;A&#34;, in it&#39;s SQL Server Agent, using the Multi-Server Administration, I set up the SQL Server Agent of &#34;A&#34; as the Master (MSX) server, and set up a Target(TSX) server pointing to the Server &#34;B&#34; that has problems in SQL Agent....referred in 1) above. When I try to &#34;Detach the MSX Server&#34; from server B, i get a message Error 207: Invalid Column Name &#34;netsend_address&#34;, 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

----------

Russell,
If you do the Master rebuild it will rebuild a fresh copy of Model and MSDB as well.  In other words, these databases will look just like they look after a fresh S2K install (not a conversion) took place.  I wouldn&#39;t bother trying to do a restore of MSDB from another system, because it may have references that are specific to that server...but like the other guy said &#34;you can certainly try.&#34;.  It may work. You don&#39;t have much to lose, right? (in MSDB, that is).

However, if you have the same jobs on the other sever that you were thinking about creating the MSDB backup from you can script the jobs out to a flat file.  Just open up the Enterprise Manager, Management then expand the SQl Server Agent node.  Right click on Jobs, select All Tasks and the select Generate SQL Script.  Create a file from this.  It will be all the jobs from that server.  Now, if you happen to have a lot of references in the script to a server that your hosed server doesn&#39;t have rights or a connection to, but all you need to do is change the server name or even a database name.  Preview the script first and you may be able to do a search and replace.  For instance, if the server that you scripted the jobs, it tries to backup Finances.  But on the server you are trying to rebuild you want to use the same script but access Accounting.  You can use search and replace in WordPad (or any other text editor) and change all occurences of Finances to Accounting. You need to study the script to see what it does.  It would probably be a good lesson on the internals of how MSDB works anyway. Does that make any sense?

To restore the jobs just open the Query Analyer.  Change the database context to MSDB, open the script you created and Execute the script.  I&#39;d do a backup of Master and MSDB before you ran this...just in case.

Hope this helps.




------------
ec at 6/14/01 3:40:00 PM

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&#39;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&#39;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&#39;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&#39;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&#39;re in bad shape especially if you have jobs or replication set up.  If you don&#39;t have a backup you can rebuild MSDB by rebuilding the Master DB.  To do that I&#39;d back up Master and Model (make 2 copies or each just for safety).  I&#39;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&#39;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 &#34;category_id&#34;
Invalid Column Name &#34;netsend_address&#34;
Invalid Column Name &#34;last_netsend_date&#34;
Invalid Column Name &#34;last_netsend_time&#34;

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

2)In another SQL Server...say &#34;A&#34;, in it&#39;s SQL Server Agent, using the Multi-Server Administration, I set up the SQL Server Agent of &#34;A&#34; as the Master (MSX) server, and set up a Target(TSX) server pointing to the Server &#34;B&#34; that has problems in SQL Agent....referred in 1) above. When I try to &#34;Detach the MSX Server&#34; from server B, i get a message Error 207: Invalid Column Name &#34;netsend_address&#34;, 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

----------

Russell,
If you do the Master rebuild it will rebuild a fresh copy of Model and MSDB as well.  In other words, these databases will look just like they look after a fresh S2K install (not a conversion) took place.  I wouldn&#39;t bother trying to do a restore of MSDB from another system, because it may have references that are specific to that server...but like the other guy said &#34;you can certainly try.&#34;.  It may work. You don&#39;t have much to lose, right? (in MSDB, that is).

However, if you have the same jobs on the other sever that you were thinking about creating the MSDB backup from you can script the jobs out to a flat file.  Just open up the Enterprise Manager, Management then expand the SQl Server Agent node.  Right click on Jobs, select All Tasks and the select Generate SQL Script.  Create a file from this.  It will be all the jobs from that server.  Now, if you happen to have a lot of references in the script to a server that your hosed server doesn&#39;t have rights or a connection to, but all you need to do is change the server name or even a database name.  Preview the script first and you may be able to do a search and replace.  For instance, if the server that you scripted the jobs, it tries to backup Finances.  But on the server you are trying to rebuild you want to use the same script but access Accounting.  You can use search and replace in WordPad (or any other text editor) and change all occurences of Finances to Accounting. You need to study the script to see what it does.  It would probably be a good lesson on the internals of how MSDB works anyway. Does that make any sense?

To restore the jobs just open the Query Analyer.  Change the database context to MSDB, open the script you created and Execute the script.  I&#39;d do a backup of Master and MSDB before you ran this...just in case.

Hope this helps.




------------
ec at 6/14/01 3:40:00 PM

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&#39;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&#39;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&#39;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&#39;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&#39;re in bad shape especially if you have jobs or replication set up.  If you don&#39;t have a backup you can rebuild MSDB by rebuilding the Master DB.  To do that I&#39;d back up Master and Model (make 2 copies or each just for safety).  I&#39;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&#39;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 &#34;category_id&#34;
Invalid Column Name &#34;netsend_address&#34;
Invalid Column Name &#34;last_netsend_date&#34;
Invalid Column Name &#34;last_netsend_time&#34;

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

2)In another SQL Server...say &#34;A&#34;, in it&#39;s SQL Server Agent, using the Multi-Server Administration, I set up the SQL Server Agent of &#34;A&#34; as the Master (MSX) server, and set up a Target(TSX) server pointing to the Server &#34;B&#34; that has problems in SQL Agent....referred in 1) above. When I try to &#34;Detach the MSX Server&#34; from server B, i get a message Error 207: Invalid Column Name &#34;netsend_address&#34;, 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

----------


## jaskens

Use program way recovery sql database fix 
Tool repair sql database any version starting with MS SQL Server 2000

----------

