Folks, this is part discovery along with a problem so pelase read my observations as what I THINK is going on. This may be part of the problem.
I needed to move the database files from one drive to another.
Using appropriate ALTER DATABASE commands I successfully moved MDF, LDF and NDF files and brought the database back online without incident.
However when running the following query to ensure nothing was missed I found an exception.
db.name AS DBName,
type_desc AS FileType,
Physical_Name AS Location
sys.databases db ON db.database_id = mf.database_id
I get a row returned that looks like this
S_Production | FULLTEXT | D:\MSSQL_Databases\S_Production_2.SearchAddress
The file ideally should be on the C drive along with the MDF,LDF,NDF.
The actual path is to a directory rather than a file.
After reading I understand this is a Full Text Catalog and when I look at the database
DBName > Storage > Full Text Catalogs > SearchAddress appears.
I get the usage of ALTER for moving files but does it apply in this case and if so what is the Logical Name and what is the path?
USE master; --do this all from the master
ALTER DATABASE S_Production
MODIFY FILE (name='SearchAddress'
,filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\S_Production_2.SearchAddress);
I'd considered rebuilding but I suspect this would just rebuild in the same location.
This is a live database using merge replication so anything I do has to be done insitu and without detach or other methods that break merge replication.
Your advice appreciated.