Back Up and Restore Full-Text Catalogs and Indexes
Applies to: SQL Server
This topic explains how to back up and restore full-text indexes created in SQL Server. In SQL Server, the full-text catalog is a logical concept and does not reside in a filegroup. Therefore, to back up a full-text catalog in SQL Server, 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 is possible to 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, simply back up its filegroup. For more information, see Backing Up and Restoring Full-Text Catalogs, in SQL Server 2005 (9.x) Books Online.
Backing Up the Full-Text Indexes of a Full-Text Catalog
Finding the Full-Text Indexes of a Full-Text Catalog
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 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 i, sys.fulltext_catalogs c
WHERE i.fulltext_catalog_id = c.fulltext_catalog_id;
GO
Finding the Filegroup or File That Contains a Full-Text Index
When a full-text index is created, it is 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 (Transact-SQL).
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 f, sys.fulltext_indexes i
WHERE f.data_space_id = i.data_space_id
and i.object_id = object_id('object_name');
GO
Backing Up the Filegroups That Contain Full-Text Indexes
After you find the filegroups that contain the indexes of a full-text catalog, you need back up each of the filegroups. During the backup process, full-text catalogs may 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.
To back up files and filegroups
Restoring a Full-Text Index
Restoring a backed-up filegroup restores the full-text index files, as well as 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.
To restore a filegroup
See Also
Manage and Monitor Full-Text Search for a Server Instance
Upgrade Full-Text Search
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho