Database Migration / Upgrade problem – Fulltext Wordbreaker, filter, or protocol handler does not exist

Long title. Sorry about that.

What?

I was migrating a few databases to a slightly upgraded server – new hardware, same OS/SQL main version, but the new one was fully service packed / updated etc. The environment dictated that I was doing this by the backup-restore method.

Restoring 27 databases; they all restored properly, but 15 of them gave a warning along these lines:

Warning: Wordbreaker, filter, or protocol handler used by catalog ‘FOOBARBAZ’ does not exist on this instance. Use sp_help_fulltext_catalog_components and sp_help_fulltext_system_components check for mismatching components. Rebuild catalog is recommended.

NB: the name displayed doesn’t necessarily match the name of any of the databases in use, just to annoy – they were (in this case) close, which is why I thought they might be…

What to do?

Firstly, check there’s really a problem by doing what the error message says – use sp_help_fulltext_catalog_components and sp_help_fulltext_system_components to check the versions of libraries that are expected by your database, and those installed on your system, respectively.

In the database that’s complaining, run the following:

EXEC sp_help_fulltext_catalog_components;

The versions the database is expecting are that the Wordbreaker is v12.0.6828.0, and the profile handler v2005.90.3042.0 (ie, the version of SQL that the server was running…)

Now, run the following to check which components / versions are actually installed on your server:

EXEC sys.sp_help_fulltext_system_components @component_type='all';

(Edited / abbreviated results):

As you can see, the versions don’t match – the installed versions of those components are v12.0.9736.0 for the wordbreaker, and v2005.90.5324.0 for the protocol handler. And, yes, I know it’s an old version of SQL Server…

How do we fix this?

(The version mis-match, thing, that is – upgrading SQL Server isn’t in scope for this project, unfortunately…)

Read the error message again. It tells you what to do.

Rebuild catalog is recommended.

Rebuild the catalog. OK.

Picking one of the databases at random, run:

SELECT * FROM sys.fulltext_catalogs AS fc

In this particular situation, I had about 15 to do, and going through each one of those 25 similarly-named databases looking for each of the fifteen full-text catalogs that may (or may not) have the same name as the database struck me as being not much fun. So, let SQL Server do the heavy lifting by generating the ALTER FULLTEXT CATALOG statements for you.

The query that needs to be executed in each database is relatively simple:

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = ISNULL(@sql, N'') + N'USE ' + QUOTENAME(DB_NAME()) + N'; ALTER FULLTEXT CATALOG ' + QUOTENAME(fc2.name) + N' REBUILD;
'
FROM sys.fulltext_catalogs AS fc2;
PRINT @sql;
--EXEC (@sql); -- yes, I'm paranoid, and want to see what query is going to be run before I actually run it.

This can be wrapped up in a call to the undocumented sp_MSforeachdb to go through and generate that query on every database with fulltext catalog(s):

EXEC sp_MSforeachdb
    '
DECLARE @sql NVARCHAR(MAX);
USE [?];
IF EXISTS (SELECT * FROM sys.fulltext_catalogs AS fc)
BEGIN
       SELECT @sql = ISNULL(@SQL, N'''') + N''USE '' + QUOTENAME(DB_NAME()) + N''; ALTER FULLTEXT CATALOG '' + QUOTENAME(fc2.name) + N'' REBUILD;
''
    FROM sys.fulltext_catalogs AS fc2;
       PRINT @sql
       --EXEC (@sql) -- Uncomment this EXEC when happy
END
' ;

NB: The last line in the command being processed is commented out, to make sure you’re happy with what’s going on before you actually do anything you might regret. Such as running code you don’t understand against your production environments. Because you would never do that, would you?

When you’re happy with the query you’re seeing, uncomment the EXEC (@sql) line.

The Results

Check your work by running the original query again:

EXEC sp_help_fulltext_catalog_components;

Success! The versions now match what we see in the results from sp_help_fulltext_system_components.

Job done. I hope. But, y’know, backup those databases before doing anything else…