While trying to backup a client database today I kept running into an error regarding the Full Text Catalog that was part of the database. I checked with our SQL guy, admitting my SQL n00b-ness, and he explained a bit about the catalog & indexes. Suffice to say, for what I'm working on I don't need the index (it's kinda in the way, to be honest).
With some new knowledge of what to Google I set off to remove my issue. And here is the 3 step process to remove that pesky Full Text Catalog for your backup purposes:
- To find what tables are using the Full Text Catalog for their indexes run this script on your database verbatim:
SELECT name, ftcatid FROM sysobjects WHERE ftcatid > 0
- TO remove the Full Text Indexes on the tables found, run this script for each table found, replacing "NAME_OF_TABLE_FOUND" with the, well, name of the table!:
EXEC sp_fulltext_table 'NAME_OF_TABLE_FOUND', 'drop'
- Once you've completed step 2 for each table connected to the catalog run this script, replacing "NAME_OF_CATALOG" with, you guessed it, the name of the catalog; Note you do NOT need quotation marks for this part:
DROP FULLTEXT CATALOG NAME_OF_CATALOG