Piecemeal Restore and Full-Text Indexes
This topic is relevant only for databases that contain multiple filegroups and, for the simple-model databases, only for read-only filegroups.
Full-text indexes are stored in database filegroups and can be affected by a piecemeal restore. If the full-text index resides in the same filegroup as any of the associated table data, piecemeal restore works as expected.
Note
To view the filegroup ID of the filegroup that contains a full-text index, select the data_space_id column of sys.fulltext_indexes.
Full-Text Indexes and Tables in Separate Filegroups
If a full-text index resides in a separate filegroup from all of the associated table data, the behavior of piecemeal restore depends on which of the filegroups is restored and brought online first:
If the filegroup that contains the full-text index is restored and brought online before the filegroups that contain the associated table data, full-text search works as expected as soon as the full-text index is online.
If the filegroup that contains the table data is restored and brought online before the filegroup that contains the full-text index, full-text behavior might be affected. This is because Transact-SQL statements that trigger a population, rebuild the catalog, or reorganize the catalog fail until the index is brought online. These statements include CREATE FULLTEXT INDEX, ALTER FULLTEXT INDEX, DROP FULLTEXT INDEX, and ALTER FULLTEXT CATALOG.
In this case, the following factors are significant:
If the full-text index has change tracking, user DML will fail until the index filegroup is brought online. Delete operation will also fail until the index filegroup is online.
Regardless of change tracking, full-text queries fail because the index is not available. If a full-text query is tried when the filegroup that contains the full-text index is offline, an error is returned.
Status functions (such as FULLTEXTCATALOGPROPERTY) succeed only when they do not have to access full-text index. For example, access to any online full-text metadata would succeed, but uniquekeycount, itemcount would fail.
After the full-text index filegroup is restored and brought online, the index data and table data are consistent.
As soon as both the base table filegroup and the full-text index filegroup are online, any paused full-text population is resumed.