Back Up and Restore Full-Text Catalogs and Indexes

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 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 Books Online.

In This Topic

  • Backing up the full-text indexes of a full-text catalog

    1. Finding the full-text indexes of a full-text catalog

    2. Finding the filegroup or file that contains a full-text index

    3. Backing up the filegroups that contain full-text indexes

  • Restoring a full-text index

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 AdventureWorks;
GO
DECLARE @TableID int;
SET @TableID = (SELECT OBJECT_ID('AdventureWorks.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

[TOP]

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

[TOP]

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

[TOP]

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

[TOP]

See Also

Concepts

Manage and Monitor Full-Text Search for a Server Instance

Upgrade Full-Text Search from SQL Server 2005