Quick note in case I need again some day. The team recently moved all the user databases for a monitoring instance to new hardware and it basically went well. Immediately afterward there were a number of error messages in the log about a missing user defined error message. Minus 5 points for not moving MSDB. We had the “old” MSDB of course, but we found this fix which took care of the problem by just adding back the error messages. Problem fixed, sorta. Turns out it was then failing differently and the new error wasn’t in the SQL log. Once I heard “service broker” it was an easy fix:
alter database ABC SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
alter database ABC SET TRUSTWORTHY ON;
There are a handful of lessons there, the least of which is about SCOM.
SQLAndy 
Thanks, may come in handy later this year
LikeLike
We recently moved the SCOM DBs as well (’tis the season, I guess). We followed this, http://blogs.technet.com/b/kevinholman/archive/2010/08/26/moving-the-operations-database-my-experience.aspx, so we knew ahead of time about service broker and the missing messages.
I didn’t do anything with the trustworthy setting though. And, it’s disabled on the original (pre-move) DB. What was the reasoning behind this change?
LikeLike
Sandra, trustworthy was required (in the link) to use the .net regex library. The better alternative is to sign it with a strong name, avoids trustworthy.
LikeLike
Hmmm… I’m still not seeing reference to trustworthy. Might just be a personal problem though.
LikeLike