Moving SQL 2000 to 2005 and Full Text Search problems

Posted At : 06 February 2008 15:55 | Posted By : Duncan Loxton
Related Categories: SQL

We have been slowly migrating databases from SQL 2000 to 2005, and as we do so we find that the Full Text Catalog isn't brought over into 2005. This causes lots of issues, one being backup stops working.

So how do you get rid of them?

To find out what tables are indexed in the database you need to do some leg work. you need to find out what tables are indexed in the database, so by looking up the catalogs in the sysobject tables, we can get a list:

SELECT * FROM sysobjects
WHERE ftcatid IN (SELECT ftcatid FROM sysfulltextcatalogs)

now run down the list of tables and run the following:

sp_fulltext_table 'TableName', 'drop'

This clears out the entry for full text searching on the tables. This leaves the catalogue clear for deletion. So lets clear the full text index catalog, and clean things up just to be tidy.

sp_fulltext_catalog 'SearchIndexName', 'drop'
go
sp_fulltext_service 'clean_up'
go