How do I remove offline Files from a SQL Server 2005 database

Question

I have a database that was restored from a SQL 2000 instance to a SQL 2005 instance some time ago. The SQL 2000 instance had old, unused Full Text Searches defined.

It appears that, when the file was restored, the full text searches were not restored but just deleted.

This left the database in a funny state, with the FTS files still associated with the database but the files the database knows about listed as OFFLINE. Which, it turns out, keeps the database from having a full backup done.

So, does anyone know how to remove the files from the database? ALTER DATABASE REMOVE FILE returns

Msg 5009, Level 16, State 2, Line 1 One or more files listed in the statement could not be found or could not be initialized.

The actual backup command throws one of the following errors for each file that doesn't exist:

Msg 9987, Level 16, State 1, Line 1 The backup of full-text catalog '' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Anyone know how to fix this problem?

Answer 1

You should have repopulated your full text catalogs. By default SQL Server 2005 does not do this to give the installation process a boost.

Answer 2

The answer to this conundrum turns out to be very simple, at least if you don't care about your FTS indices, which I don't.

EXEC master.dbo.sp_detach_db @dbname = N'dbname', @keepfulltextindexfile=N'false'

The key here is the second flag, @keepfulltextindexfile, which defaults to true. By setting that to false and then reattaching the database, all FTS data is dropped and backups can work the way they should.