Update to moving OperationsManager Database Steps

It was discovered by some of my colleagues, that when they completed the steps to move the OperationsManager database from one SQL Server computer to another, several events are written to the Application Event Log.  An example of those events is:

Event Type:  Error
Event Source:  MSSQLSERVER
Event Category:  (2)
Event ID:  18054

Description:

Error 7779800008, severity 16, state 1 was raised, but no message with that error number was found in sys.messages.  If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

This event is generated because when we documented the steps to move the OperationsManager database from one SQL Server computer to another in the Operations Manager 2007 Backup and Recovery Guide, we were not aware of the fact that some specific configuration information is stored in the Master database. 

You see during Operations Manager setup, after the OperationsManager database is created, error messages specific to Operations Manager are not tied to the database, but are stored in the sys.messages Catalog View in the Master database. 

We are in the process of updating the Operations Manager 2007 Backup and Restore Guide accordingly.  In the meantime, if you are preparing to perform this operation, you will need to execute the attached snippet of code.  This snippet of code will ensure that Error Messages specific to the OperationsManagerDB are available on the new SQL Server.  Open the attached file in SQL Server 2005 Query Designer and execute against the Master database.

This posting is provided "AS IS" with no warranties, and confers no rights.

Fix_OpsMgrDB_ErrorMsgs.SQL.sql