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

Back Up and Restore Full-Text Catalogs and Indexes

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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

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

Community Additions

ADD
Show:
© 2015 Microsoft