INF: Rebuilding the MSDB Database in SQL Server 6.0 and 6.5

In some cases it may be necessary to rebuild the MSDB database that maintains the task, alert, and operator information for SQL Executive.

After you discuss the situation with a SQL Server support professional, if the MSDB database cannot be reloaded from a backup and does need to be rebuilt from scratch, you should follow the steps below. Note that any existing tasks, alerts, and operators will be lost and will have to be added to the system. Also note that some of the tasks may be associated with replication, and that publisher, distributor, and subscriber information is not held in the MSDB database; you should NOT use this process to remove replication or to terminate a subscription or publication.

In the case where it has been determined that any MSDB triggers or stored procedures have been lost or damaged and must be recreated (but the MSDB devices, database, and tables are all reliable), it is safe to re-run the INSTMSDB.SQL script on an existing MSDB database because it will not destroy any data. Be aware that if you re-run INSTMSDB.SQL, the default alerts will be re-installed, and object permissions (which should not have been altered) will be reset.

The steps to rebuild an MSDB database by replacing it are:

Using SQL Enterprise Manager (SEM):

1. Stop SQL Executive (use the right mouse button on SQL Executive; click Stop).
2. Delete the existing MSDB database (right mouse button on MSDB database; click Delete, but do not delete the database devices).
3. Run the script \SQL60\INSTALL\INSTMSDB.SQL use the Query Analyzer; this script will create the database and the necessary tables; if necessary, replace 'sql60' in the path with the installation directory where you have installed the SQL Server files. (For SQL Server 6.5, this would be 'MSSQL').
4. If any errors occur, save the Query Analyzer output in a XX.RPT file.
5. For SQL Server 6.0, run the script \SQL60\INSTALL\SERVMSG.SQL. If this is SQL Server 6.5, run the script \MSSQL\INSTALL\SERVMSGS.SQL (This script will create the error messages for SQL Executive in msdb..sysservermessages).
6. Start SQL Executive (right mouse button on SQL Executive; click Start).
7. Add a task, an alert, and an operator to test the system.