File Backup and Restore and Full-Text Catalogs

 This topic is relevant only for databases that contain multiple filegroups and, for the simple-model databases, only for read-only filegroups.

Full-text catalogs exist as files in a filegroup and can be affected by a piecemeal restore. Piecemeal restore allows for primary and, optionally, one or more secondary filegroups to be brought online; additional filegroups can be brought online later.

Best Practice We recommend that you store table data and any affiliated full-text catalogs in the same filegroup. If this best practice is observed in a piecemeal restore, bringing the filegroup including the full-text catalog back online is simple: both the table data and full-text catalog data are restored and recovered. From a full-text perspective, nothing special must be done.

Full-Text Catalogs and Tables in Separate Filegroups

If a full-text catalog exists in a separate filegroup from any filegroup that contains associated table data, the behavior of piecemeal restore depends on which filegroup is restored and brought online first. If the filegroup that contains the full-text catalog is restored and brought online first, nothing occurs to the catalog and nothing has to be done. At some later point, the filegroup that contains the table data can be restored and brought online, if the catalog data and table data is consistent no population is required. Otherwise, populations are issued for all tables in the catalog to make catalog and tables consistent.

In contrast, if the filegroup that contains the table data is restored and brought online before the filegroup that contains the associated full-text catalog and full-text index, full-text behavior might be affected. Because the catalog is offline, any Transact-SQL DDL statement that triggers a population, rebuild catalog, or reorganize catalog fails, such statements include CREATE FULLTEXT INDEX, ALTER FULLTEXT INDEX, DROP FULLTEXT INDEX, and ALTER FULLTEXT CATALOG. For more information about these DDL statements, see Full-Text Transact-SQL Enhancements. In this case, the following variables are significant:

• Change tracking
  User DML to the table are not affected. Changes are recorded. However, the AUTO or UPDATE POPULATION options do not run until the catalog filegroup is brought online.
• Regardless of change tracking, full-text queries fail, because the catalog is not available. The failure typically occurs when full-text tries to mount the unavailable catalog. If a full-text query is tried when the filegroup that contains the full-text category is offline, an error is returned.
• Status functions (such as FULLTEXTCATALOGPROPERTY) succeed when they do not have to mount the MSSearch catalog. For example, access to any online full-text metadata would succeed, but uniquekeycount would fail.

After the full-text catalog filegroup is restored and brought online, if the catalog data and table data are consistent, no population is issued. Otherwise, populations are automatically started for all tables in the catalog to make catalog and tables consistent.

See Also

Other Resources

Backing Up and Restoring Full-Text Catalogs

Help and Information

Getting SQL Server 2005 Assistance