When migrating a database, via backup from a sql server 2000 instance to a sql server 2005 instance, the full-text catalogs must be rebuilt.

This is most evident when a full backup fails with the following error:

System.Data.SqlClient.SqlError: The backup of full-text catalog 'DevStudio' 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.  (Microsoft.SqlServer.Smo)

To correct this error you can uses pieces of the following script, ensure to change your database, paths and catalogs as appropriate for your environment:

EXEC sp_help_fulltext_catalogs

-- SELECT name FROM sys.fulltext_catalogs;

SELECT FULLTEXTCATALOGPROPERTY('DevStudio','PopulateStatus')

sp_help_fulltext_tables;

sp_help_fulltext_tables @fulltext_catalog_name = 'DevStudio'; -- all the tables linked to that full-text catalog

-- select * from sys.fulltext_indexes

sp_help_fulltext_columns;

sp_help_fulltext_columns @table_name = 'XmlFile' -- returns list of all the columns for a single table

-- select * from sys.fulltext_index_columns

create FULLTEXT CATALOG [CDD]

in PATH N'D:\Shr2k5a\data\FTSearch'

AUTHORIZATION [dbo]

CREATE FULLTEXT INDEX ON XmlFile(Type, Content)

KEY INDEX PK_XmlFile

ON CDD;

EXECUTE sp_fulltext_catalog 'CDD', 'Rebuild';

-- NOTE: The sp_fulltext_database stored procedure with the ENABLE option may be used to rebuild all known full-text catalogs

EXECUTE sp_fulltext_catalog 'CDD_07', 'start_full';

DROP FULLTEXT INDEX ON dbo.XmlFile;

DROP FULLTEXT CATALOG DevStudio;