Edit

Back up and restore full-text catalogs and indexes

Applies to: SQL Server

This article explains how to back up and restore full-text indexes created in the SQL Server Database Engine. The full-text catalog is a logical concept and doesn't reside in a filegroup. Therefore, to back up a full-text catalog, you must identify every filegroup that contains a full-text index that belongs to the catalog. Then you must back up those filegroups, one by one.

Important

You can import full-text catalogs when upgrading a SQL Server 2005 (9.x) database. Each imported full-text catalog is a database file in its own filegroup. To back up an imported catalog, back up its filegroup, in SQL Server 2005 (9.x) Books Online.

Back up the full-text indexes of a full-text catalog

Find the full-text indexes of a full-text catalog

To retrieve the properties of the full-text indexes, use the following SELECT statement, which selects columns from the sys.fulltext_indexes and sys.fulltext_catalogs catalog views.

USE AdventureWorks2025;
GO

DECLARE @TableID AS INT;

SET @TableID = (SELECT OBJECT_ID('AdventureWorks2025.Production.Product'));

SELECT object_name(@TableID),
       i.is_enabled,
       i.change_tracking_state,
       i.has_crawl_completed,
       i.crawl_type,
       c.name AS fulltext_catalog_name
FROM sys.fulltext_indexes AS i, sys.fulltext_catalogs AS c
WHERE i.fulltext_catalog_id = c.fulltext_catalog_id;
GO

Find the filegroup or file that contains a full-text index

When a full-text index is created, it's placed in one of the following locations:

  • A user-specified filegroup.
  • The same filegroup as base table or view, for a nonpartitioned table.
  • The primary filegroup, for a partitioned table.

Note

For information about creating a full-text index, see Create and manage full-text indexes and CREATE FULLTEXT INDEX.

To find the filegroup of a full-text index on a table or view, use the following query, where object_name is the name of the table or view:

SELECT name
FROM sys.filegroups AS f, sys.fulltext_indexes AS i
WHERE f.data_space_id = i.data_space_id
      AND i.object_id = object_id('object_name');

Back up the filegroups that contain full-text indexes

After you find the filegroups that contain the indexes of a full-text catalog, back up each filegroup. During the backup process, full-text catalogs might not be dropped or added.

The first backup of a filegroup must be a full file backup. After you create a full file backup for a filegroup, you can back up only the changes in a filegroup by creating one or more differential file backups based on the full file backup.

Back up files and filegroups

Restore a full-text index

Restoring a backed-up filegroup restores the full-text index files and the other files in the filegroup. By default, the filegroup is restored to the disk location where it was backed up.

If a full-text indexed table was online and a population was running when the backup was created, the population resumes after the restore.

Restore a filegroup

Related content

  • Last updated on