Many application users define custom errors in their applications for better error handling . These custom messages are stored in master.sys.messages in SQL Server. When such SQL Server databases are migrated, we also have to consider migrating custom messages and since these messages are stored in master database, so it will not get auto migrated onto new SQL Server and to be taken care manually. In this article we will see how to migrate such custom messages in new SQL Server.

Query to check custom messages:

SELECT sm.message_id,
sm.language_id,
sm.severity,
sm.is_event_logged,
sm.text
FROM sys.messages AS sm
WHERE sm.message_id > 50000 –message IDs less than 50000 are system messages.

If above query returns any output, then that will be custom message and same need to be created on new SQL Server as part of migration. But in case, if above query does not return any output, then it shows that no custom messages are defined and no action to be taken in such case.

Suppose, we get below output when we run above command in old SQL Server. In such case, we will have to check these values and pass it in sp_addmessage and run it on new SQL Server.

Adding Custom messages:

use master
go
exec sp_addmessage @msgnum = 60000, @severity = 16,
@msgtext = N'The item named %s already exists in %s.'

Sample Output( in new SQL Server):

More details about the sp_addmessage option can be found here

Dropping Custom messages:

Syntax:

sp_dropmessage [ @msgnum = ] message_number
[ , [ @lang = ] 'language' ]

Example:

USE master;
GO
EXEC sp_dropmessage 50001;