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 |
|---|
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:
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 AdventureWorks2008R2;
GO
DECLARE @TableID int;
SET @TableID = (SELECT OBJECT_ID('AdventureWorks2008R2.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
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 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');
GO
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
SELECT fg.data_space_id, fg.name FilegroupName , object_name(i.object_id) IndexedTableName, c.name IndexOrCatalogName
FROM sys.data_spaces fg
INNER JOIN sys.fulltext_indexes i on fg.data_space_id = i.data_space_id
INNER JOIN sys.fulltext_catalogs c on i.fulltext_catalog_id = c.fulltext_catalog_id
WHERE objectproperty(i.object_id,'IsUserTable') = 1
UNION ALL
SELECT fg.data_space_id, fg.name FilegroupName , object_name(i.object_id) IndexedTableName, i.name IndexOrCatalogName
FROM sys.data_spaces fg
INNER JOIN sys.indexes i on fg.data_space_id = i.data_space_id
INNER JOIN sys.fulltext_indexes ti on i.object_id = ti.object_id
WHERE objectproperty(i.object_id,'IsUserTable') = 1
- 3/17/2012
- JManuelN
The initial query given uses a non standard Join syntax and also uses @TableId to give incorrect results.
The query should be
USE AdventureWorks2008R2;
GO
SELECT OBJECT_NAME(Object_Id) AS ObjectName, i.is_enabled, i.change_tracking_state,
i.has_crawl_completed, i.crawl_type, c.name AS fulltext_catalog_name
FROM sys.fulltext_indexes AS i
INNER JOIN sys.fulltext_catalogs AS c ON i.fulltext_catalog_id = c.fulltext_catalog_id
GO
- 2/11/2012
- Patrick D Flynn
