Scripting COMPLETE Full-Text indexes after a database restore

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.

 

--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'

 

Leave a Reply

Your email address will not be published. Required fields are marked *