You may find yourself working with SQL 2000 databases, or later versions of SQL that were migrated from SQL 2000, with the Full-Text indexes stored outside the SQL data files.
In this scenario, once you restore a SQL Server database, your full-text catalogs and the full-text indexes within are not restored along with your data.
Note that this is not required for Full-Text indexes created in SQL 2005 and later, when the storage for Full-Text Catalogs was changed.
Right-Clicking and choosing to script the full-text catalog will NOT create the indexes within the catalog.
I found a useful script at humakhurshid.blog.com that scripts out the full-text catalog AND indexes. EDIT: However it misses some indexes, I found a better script at the bottom of the comment thread at this blog at blog.strictly-software.com
This script at mikesdatawork.wordpress.com will rebuild the full-text catalogs for all those newly created full-text indexes. This may be a useful maintenance plan script as well.
I had full-text catalogs with names that include spaces (FT Index) so I had to modify it slightly (added these brackets: [” + sftc.name + ”] ) to work.
Of course now those indexes need to populate. Found this stackoverflow post that gives a solution for one database, here’s my query to check population status for all catalogs.
[codesyntax lang=”sql” capitalize=”upper” title=”Check status of all fulltext catalogs on your SQL Server.”]
--Check all fulltext catalogs on server. Ok to run as a single line. --1 means populating, 0 means idle EXEC sp_MSForEachDB 'SELECT FULLTEXTCATALOGPROPERTY(cat.name,''PopulateStatus'') as [?] FROM [?].sys.fulltext_catalogs AS cat'