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