Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Backing Up and Restoring a SQL Server 2008 Full-Text Catalog

This topic explains how to back up and restore full-text indexes created in SQL Server 2008. In SQL Server 2008, 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 2008, you must identify every filegroup that contains a full-text index of the catalog and back them up, one by one.

Important noteImportant

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.

Backing up and restoring the indexes of a SQL Server 2008 full-text catalog involves the following steps:

  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 full-text indexes of a full-text catalog

  4. Restoring a full-text index

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;
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;

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.


For information about creating a full-text index, see CREATE FULLTEXT INDEX (Transact-SQL) or How to: Create Full-Text Indexes (Visual Database Tools).

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');

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 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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft