| title | Back up & Restore Full-Text Catalogs and Indexes | ||||||
|---|---|---|---|---|---|---|---|
| description | Learn how to back up and restore full-text indexes created in SQL Server. | ||||||
| author | rwestMSFT | ||||||
| ms.author | randolphwest | ||||||
| ms.reviewer | mikeray | ||||||
| ms.date | 08/28/2025 | ||||||
| ms.service | sql | ||||||
| ms.subservice | search | ||||||
| ms.topic | how-to | ||||||
| helpviewer_keywords |
|
[!INCLUDE SQL Server]
This article explains how to back up and restore full-text indexes created in [!INCLUDE ssNoVersion]. In [!INCLUDE ssNoVersion], the full-text catalog is a logical concept and doesn't reside in a filegroup. Therefore, to back up a full-text catalog in [!INCLUDE ssNoVersion], 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
It's possible to import full-text catalogs when upgrading a [!INCLUDE ssVersion2005] 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 [!INCLUDE ssVersion2005] Books Online.
You can retrieve the properties of the full-text indexes by using the following SELECT statement, which selects columns from the sys.fulltext_indexes and sys.fulltext_catalogs catalog views.
USE AdventureWorks2022;
GO
DECLARE @TableID AS INT;
SET @TableID = (SELECT OBJECT_ID('AdventureWorks2022.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;
GOWhen 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 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');After you find the filegroups that contain the indexes of a full-text catalog, you need to back up each of the filegroups. 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 have created a full file backup for a filegroup, you could back up only the changes in a filegroup by creating a series of one or more differential file backups that are based on the full file backup.
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 on which the filegroup was backed up.
If a full-text indexed table was online, and a population was running when the backup was created, the population is resumed after the restore.