Recently I had to migrate our primary databases to a new server. Yaay for the faster hardware, everything went smoothly until the next day when users started reporting that searches were failing. Turned out to be the full text catalogs do not move over (that small bit got overlooked) when you restore a db. On top of that, the paths had changed, and drives that existed before did not exist any more.
Neither SSMS or Enterprise Manager would allow me to use the graphical interface to change the paths; nor could I delete the catalog and rebuild it, nay, SQL Server said, the path was invalid, you can't touch this. So what to do. Here is what I got:
-- this has to be run when connected to the db you want to update, ie:
AdventureWorks
-- to manually override an invalid path setting for a full text catalog
-- run SELECT * FROM sysfulltextcatalogs to get the ftcatid of the catalog you want to change
SELECT * FROM sysfulltextcatalogs
-- 1)
sp_configure 'allow update', '1'
RECONFIGURE WITH OVERRIDE
-- 2) use the ftcatid retrieved from above
update sysfulltextcatalogs set path='D:\MSSQL' WHERE ftcatid = 10
-- 3)
sp_configure 'allow update', '0'
RECONFIGURE WITH OVERRIDE
Fairly straight-forward and pretty painless...
SELECT * FROM sysfulltextcatalogs will give you a list of all the catalogs for the current database (FYI: use the database you need to work with, so if it is a FT catalog for AdventureWorks,
USE AdventureWorks first, then run this). Just eyeball the resultset, find the ftcatid you need to manipulate, then modify the rest of the script as necessary.
Posted
Apr 10 2008, 06:41 PM
by
dacrowlah