When SCOM installs its databases it also creates a bunch of sys.messages. This can cause trouble when you migrate SCOM to another server or when setting up an AlwaysOn Availability Group; because after failover the messages aren't available.
I found a couple hackish ways to extract and copy the messages but I wanted to do something better. I wanted to extract everything from the server rather than have to depend on a script that might be out of date, but then also verify whether there are any mismatches from prior bad imports.
I came up with some code that will generate the actual code.
Declare @NewLine Char(2) = Char(13) + Char(10)
Select Top 10 'If Exists (Select Top 1 0 From master.sys.messages Where message_id = ' + Cast(message_id As Nvarchar(max)) + ' And language_id = ' + Cast(language_id As Nvarchar(Max)) + '' + ' And severity = ' + Cast(severity As Nvarchar(Max)) + ')' + @NewLine +
'Begin' + @NewLine +
' If Exists (Select Top 1 0 From master.sys.messages Where message_id = ' + Cast(message_id As Nvarchar(max)) + ' And language_id = ' + Cast(language_id As Nvarchar(Max)) + '' + ' And severity = ' + Cast(severity As Nvarchar(Max)) + ' And (is_event_logged <> ' + Cast(is_event_logged As Nvarchar(Max)) + ' Or [text] <> N''' + Replace([text], '''', '''''') + '''))' + @NewLine +
' Begin' + @NewLine +
' Print ''Mismatch in message_id ' + Cast(message_id As Nvarchar(Max)) + ' language_id ' + Cast(language_id As Nvarchar(Max)) + ' severity ' + Cast(severity As Nvarchar(Max)) + '''' + @NewLine +
' End' + @NewLine +
'End' + @NewLine +
'Else ' + @NewLine +
'Begin' + @NewLine +
' Exec master.sys.sp_addmessage @msgnum = ' +
Cast(message_id As Nvarchar(max)) +
', @msgtext = N''' + Replace([text], '''', '''''') +
''', @severity = ' + Cast(severity As Nvarchar(Max)) +
', @lang = N''' + syslanguages.name + '''' +
Case When syslanguages.name = N'us_english' Then ', @with_log = ' + Case When is_event_logged = 0 Then 'False' Else 'True' End Else '' End + @NewLine +
'End' + Char(13) + Char(10)
From sys.messages
Left Join sys.syslanguages
On messages.language_id = syslanguages.msglangid
Where message_id Like '7779%'
Order By Case When syslanguages.name = N'us_english' Then 0 Else 1 End
Here's a sample of a bit of the output (I don't have SCOM installed on this server so it's another message I've pulled out). It shows you how it compares the settings to report on any problems rather than just replacing willy-nilly.
If Exists (Select Top 1 0 From master.sys.messages Where message_id = 49917 And language_id = 1033 And severity = 10)
Begin
If Exists (Select Top 1 0 From master.sys.messages Where message_id = 49917 And language_id = 1033 And severity = 10 And (is_event_logged <> 1 Or [text] <> N'Default collation: %ls (%ls %u)'))
Begin
Print 'Mismatch in message_id 49917 language_id 1033 severity 10'
End
End
Else
Begin
Exec master.sys.sp_addmessage @msgnum = 49917, @msgtext = N'Default collation: %ls (%ls %u)', @severity = 10, @lang = N'us_english', @with_log = True
End