Knowledge Base

How to move, copy, and back up full-text catalog folders and files

Article ID: 240867

Article Last Modified on 11/30/2007

APPLIES TO

Microsoft SQL Server 2000 Standard Edition, when used with:
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Enterprise Edition
Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q240867

Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SUMMARY

Full-text catalogs and indexes are not stored in a SQL Server database. Full-text catalogs and indexes are stored in separate files that the Microsoft Search service manages. The full-text catalog files are not recovered during a Microsoft SQL Server recovery. Additionally, you cannot use the Transact SQL statements BACKUP and RESTORE to back up and to restore full-text catalog files. After recovery or restore operations, you must separately resynchronize the full-text catalogs. Only the Microsoft Windows NT system administrator and the Microsoft Search service can access the full-text catalog files.

Although you cannot use SQL Server databases to back up full-text catalogs, you can use production databases with large tables that are full-text search-enabled. If you do so, you might have to move, back up, or restore these full-text catalogs and their production databases. The run time for a full-text catalog population or resynchronization may be longer than the typical maintenance window. This article describes a different method that you can use to move or to copy the catalogs without initiating a full population. This method avoids a significant downtime.

This article documents several methods that you can use to copy, move, and back up or restore full-text catalog folders and files in certain restrictions:

The SQL Server versions that you are using are the same.
Full-text catalog folders and files must be on a local drive.
Full-text catalog folders and files must retain their original names and folder contents.
The full-text enabled database identifier and table identifier must be the same on both servers when you move or copy full-text catalogs between servers.

Warning These procedures are not supported on instances of SQL Server Failover Cluster.

Full-text catalogs are maintained as a collection of folders and files. The default locations of full-text catalogs are:

Microsoft SQL Server 7.0:
\Mssql7\FTDATA

Microsoft SQL Server 2000:
Default instance: Program Files\Microsoft SQL Server\MSSQL\FTDATA
Named instance: Program Files\Microsoft SQL Server\MSSQL$instancename\FTDATA

Each full-text catalog and its associated files are maintained under a folder that uses the following naming convention where dbid is the associated database identifier, and where ftcatid is the full-text catalog identifier:

"SQLxxxxx(dbid)yyyyy(ftcatid)"

Each ftcatid is unique in the associated database.

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To back up a full-text catalog on a stand-alone computer or a cluster

Stop the Microsoft Search service on a stand-alone computer. For a clustered SQL Server configuration, leave the full-text resource online. After you stop the Microsoft Search service, you cannot run full-text queries that use the predicates CONTAINS or FREETEXT, or the rowset-valued functions CONTAINSTABLE or FREETEXTTABLE against the database. Additionally, full-text search queries do not function, and you receive the following error message:

Server: Msg 7602, Level 16, State 1, Line 2 The Full-Text Service (Microsoft Search) is not available. The system administrator must start this service.
Use a Microsoft Windows NT file system backup utility, such as Backup Exec, and then back up the full-text catalogs, folders, and files.The system table for full-text catalogs (sysfulltextcatalogs) is backed up with the SQL Server database.
Back up the following registry entries if you must back up the catalogs individually:

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Applications\SQLServer\Catalogs\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\CatalogNames\SQLServer\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\Projects\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer\[FT_Catalog_Folder] [FT_Catalog_Folder] is the catalog folder name "SQLxxxxx(dbid)yyyyy(ftcatid)"
For a named instance of SQL Server, the registry entry is \SQLServer$InstanceName\.
2. Back up the following registry entries to back up all the catalogs: (This backs up all full-text catalogs on the server.)

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Applications\SQLServer\Catalogs \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\CatalogNames\SQLServer \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\Projects \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer For a named instance of SQL Server, the registry entry is \SQLServer$InstanceName\.

To restore full-text catalogs on the same server to the same local drive or path

Stop the Microsoft Search service. Note Perform step 2 only if the database was dropped. Otherwise, go to step 3.
Restore or attach the database where you enabled full-text. After you restore the database, make sure the database ID (dbid) is the same as it was when you backed it up.
Note If the database was attached by using the sp_attach_db command, make sure that you run the exec sp_fulltext_database 'enable' command to enable the database for full-text.
To determine the dbid, run the following code:
```
Use dbname
go
select db_id()
```
Note To restore individual catalogs, follow step 4. To restore all catalogs, go to step 5.
Restore the registry entries that you backed up based on the earlier backup instructions. You may want to back up your registry before you try this step.

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Applications\SQLServer\Catalogs\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\CatalogNames\SQLServer\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\Projects\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer\[FT_Catalog_Folder] [FT_Catalog_Folder] is the catalog folder name "SQLxxxxx(dbid)yyyyy(ftcatid)"
For a named instance of SQL Server, the registry entry is \SQLServer$InstanceName\.
To restore all catalogs on the server, restore the following registry entries. You may want to back up your registry before you try this step.

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Applications\SQLServer\Catalogs \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\CatalogNames\SQLServer \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\Projects \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServerFor a named instance of SQL Server, the registry entry is \SQLServer$InstanceName\.
Restore the catalogs from backup to the default location that you backed it up from.
Restart the Microsoft Search service.
Confirm that the new full-text catalogs folders and files are functional by using a Transact-SQL SELECT statement and use a CONTAINS or FREETEXT predicate in the WHERE clause.

To move or copy full-text catalogs between local drives or paths on the same computer that is running SQL Server

Confirm the full-text catalog name and local drive or path by using sp_help_fulltext_catalogs 'FT_Catalog_Name'. Record the full-text catalog drive letter and fully qualified path to use later.
Stop the Microsoft Search service. Perform step 3 and 4 only if the database was dropped or if the catalog was deleted, otherwise, go to step 5.
Restore or attach the database where you enabled full-text. After you restore the database, make sure the database ID (dbid) is the same as it was when you backed it up.

Note If the database was attached by using the sp_attach_db command, make sure that you run the exec sp_fulltext_database 'enable' command to enable the database for full-text.

To determine the dbid, run the following code:
```
Use dbname
go
select db_id()
```
Restore the following registry entries. You may want to back up your registry before you try this step.

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Applications\SQLServer\Catalogs\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\CatalogNames\SQLServer\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\Projects\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer\[FT_Catalog_Folder] [FT_Catalog_Folder] is the catalog folder name "SQLxxxxx(dbid)yyyyy(ftcatid)"
For a named instance of SQL Server, the registry entry is \SQLServer$InstanceName\.
Determine the new full-text catalog local drive or path. Copy the full-text catalogs to the new location.
Allow system table updates by using the system stored procedure sp_configure and RECONFIGURE with override, and then update the [database_name].dbo.sysfulltextcatalogs path column to the new local drive or path destination for the full-text catalog default folder, such as d:\FTData.
Use the Windows NT 4.0 Resource Kit regfind utility to find and replace the following HKLM registry key values for each full-text catalog folder (FT_Catalog_Folder) (for example, SQL0000500005):

\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer\[FT_Catalog_Folder] \SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\Projects\[FT_Catalog_Folder] \SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer\[FT_Catalog_Folder]

Additionally, make sure to use straight quotation marks ("") to enclose the registry keys for both the -p and -r parameters. For example:
```
-m \\server_2 -p "<Key Name>" <Old_FT_Catalog_Path_and_Folder> -r <New_FT_Catalog_Path_and_Folder>
```
Note: Replace <Key Name>, <Old_FT_Catalog_Path_and_Folder>, and <New_FT_Catalog_Path_and_Folder> with the appropriate values.
For example: -m \\server_2 -p "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\sqlserver$SQL2k\SQL0000500005" d:\programme\Microsoft SQL Server\MSSQL$SQL2K\FTDATA\SQL0000500005 -r d:\programme\Microsoft SQL Server\MSSQL$SQL2K\FTDATA\New Location\SQL0000500005

For more information about the syntax for using the regfind utility, type the following parameter from a DOS command prompt: regfind /?
Restart the Microsoft Search Service.
Confirm that the new full-text catalogs folders and files are functional by using a Transact-SQL SELECT statement and use a CONTAINS or FREETEXT predicate in the WHERE clause.

To copy full-text catalogs between instances of SQL Server to the same local drive or path

The following steps demonstrate how to move or copy a full-text catalog folder and files from one computer that is running SQL Server (server_1) to another computer that is running SQL Server (server_2). This article assumes that SQL Server has full-text search installed and that the same database and table identifiers exist on both servers. For the successful copy of full-text catalogs from one instance of SQL Server to another, the database IDs must be the same. Therefore, it is best to have server_2 (to which you are copying the catalog) be an exact replica (clone) of the server_1.Confirm that the dbids on both server_1 and server_2 are the same. To do so, run the following commands on both servers:

Use dbname
go
select db_id()

To "swap" dbids to obtain the correct dbid on server_2, use sp_detach_db and sp_attach_db to detach and to re-attach the correct databases. For example, if you have two databases, such as TestDB (dbid = 7) and PerfDB (dbid = 8), detach both databases, and then first re-attach the PerfDB database, and then the TestDB database. This swaps the dbids: The dbid for TestDB becomes 8 and the dbid for PerfDB becomes 7. The lowest dbid in the pool of available dbids is always used first.

Caution To do this, before you detach the databases, make sure that both databases do not have any full-text catalogs and that they are not full-text enabled. See "Clean-up Procedures" in the “More Information” section for steps to drop full-text catalogs and to disable full-text for a database.

Confirm that the master.dbo.sysdatabases dbid values for the full-text enabled databases are the same on both servers.
Confirm that the [database_name].dbo.sysobjects ID values for the full-text enabled tables are the same on both servers. For more information about how to obtain a table ID value, see the "object_id" topic in SQL Server Books.
Confirm that the full-text catalog name and local drive or path are the same on both servers by using sp_help_fulltext_catalogs 'FT_Catalog_Name'.
Stop the Microsoft Search service on both server_1 and server_2.
Map a drive letter on server_1 to the corresponding drive or path on server_2.
Copy the full-text catalog folder and files that you identified in step 3 from server_1 to server_2 by using the DOS command xcopy. Use the fully qualified full-text catalog drive or path and folder name as the source location and the mapped full-text catalog drive or path and folder name as the destination location. If the destination folders do not exist on server_2, use the xcopy switches /I and /E to create all destination folders. To restore all catalogs, copy all catalog folders to server_2.
You can use the DOS command RMDIR /S /Q [FT_Catalog_drive/path] on server_1 to remove the full-text catalog files on server_1.

Note To restore individual catalogs, follow step 8. To restore all catalogs, go to step 9.
Restore the registry entries that you backed up based on earlier instructions. You may want to back up your registry before you try this step.

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Applications\SQLServer\Catalogs\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\CatalogNames\SQLServer\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\Projects\[FT_Catalog_Folder] \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer [FT_Catalog_Folder] [FT_Catalog_Folder] is the catalog folder name "SQLxxxxx(dbid)yyyyy(ftcatid)"
For a named instance of SQL Server, the registry entry is \SQLServer$InstanceName\.
To restore all catalogs on the server, restore the following registry entries. However, if you do so, you lose all existing catalogs on server_2 after you perform this step. You may want to back up your registry before you try this step.

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Applications\SQLServer\Catalogs \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\CatalogNames\SQLServer \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\Projects \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer

Note By default, the locations of full-text binaries are:
SQL Server 7.0: \Mssql7\FTDATA
Microsoft. SQL Server 2000 default instance: \Program Files\Microsoft SQL Server\MSSQL\FTDATA
SQL Server 2000 named instance: MSSQL$InstanceName\FTDATA
These locations and folder paths may vary for different computers. In this case, you must change two registry entries so the paths point to the correct location of the full-text binaries after you complete the restore on server_2.

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\CatalogNames\SQLServer \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer

You can use the regfind utility to find registry entries and change the location of the full-text binaries.
Restart the Microsoft Search service on both servers.
Confirm that the new full-text catalogs folders and files are functional by using a Transact-SQL SELECT statement and use a CONTAINS or FREETEXT predicate in the WHERE clause.

MORE INFORMATION

The following SQL Server script demonstrates how to move or "migrate" a full-text catalog folder from SQL Server 7.0 (server_1) to another computer that is running SQL Server 7.0 (server_2) to a different local drive or path location on server_2. This article assumes that SQL Server 7.0 with full-text search is installed on a Windows NT 4.0-based computer, and that the same database and table identifiers exist on both servers.

For the purposes of this illustration, the Pubs (dbid = 5) database and table pub_info (id = 645577338) are full-text enabled and populated on both servers. Replace server_2 with your server name and replace the full-text catalog folder name and drive or path with your full-text catalog folder name and drive or path.

You must log on as a member of the server's local Administrators group and be a member of the SQL Server sysadmin server role (or log on as "sa"). Additionally, you must be the database owner (DBO) of the database to run the following SQL Server scripts.

Run the following SQL Server script on both servers:

use pubs
go
sp_fulltext_service 'clean_up'
go
sp_fulltext_database 'enable'
go

-- Creates and activates the full-text catalog: PubInfo, if it does not exist.
-- Drops, re-creates and activates the full-text catalog: PubInfo, if it does
-- exist.
IF OBJECTPROPERTY ( object_id('pub_info'),
                    'TableHasActiveFulltextIndex') = 1
BEGIN
    print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index
& Catalog...'
    EXEC sp_fulltext_table 'pub_info', 'drop'
    EXEC sp_fulltext_catalog 'PubInfo', 'drop'
    print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
    EXEC sp_fulltext_catalog 'PubInfo', 'create'
    EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
    EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
    EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
    EXEC sp_fulltext_table 'pub_info', 'activate'
END
ELSE IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex') = 0
BEGIN
    print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
    EXEC sp_fulltext_catalog 'PubInfo', 'create'
    EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
    EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
    EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
    EXEC sp_fulltext_table 'pub_info', 'activate'
END
go -- Confirm Database ID, Object ID, FT Catalog ID and FT folder(default)
   -- location.
select dbid, name from master.dbo.sysdatabases where dbid = DB_ID('pubs')
-- dbid = 5
go
select id, name from pubs.dbo.sysobjects where id = object_id('pub_info') 
go
sp_help_fulltext_catalogs 'PubInfo' 
go
sp_help_fulltext_tables 'PubInfo', 'pub_info' 
go
sp_help_fulltext_columns 'pub_info' 
go
exec master..xp_cmdshell 'dir d:\MSSQL70\FTDATA'
go

--- After full-text is enabled and activated, start full crawl/population
BEGIN
SET NOCOUNT ON
EXEC sp_fulltext_catalog 'PubInfo', 'start_full'
--
--  Wait for crawl to complete
--  NOTE: Forlarger tables, increase the WAITFOR DELAY time appropriately
--
DECLARE @status int, @itemCount int, @keyCount int, @indexSize int
SELECT @status = FullTextCatalogProperty('PubInfo', 'populatestatus')
WHILE (@status <> 0)
BEGIN
  WAITFOR DELAY '00:00:01' -- wait for 1 second before checking FT
                           -- Populatestatus...
  SELECT @status = FullTextCatalogProperty('PubInfo', 'populatestatus')
END
WAITFOR DELAY '00:00:05' -- wait for 5 seconds to receive correct FT Property
                         -- info (add more time for larger tables)...
SET @itemCount = FullTextCatalogProperty('PubInfo', 'itemcount')
SET @keyCount = FullTextCatalogProperty('PubInfo', 'uniquekeycount')
SET @indexSize = FullTextCatalogProperty('PubInfo', 'indexsize')
PRINT 'Nbr. of Rows FT Indexed = ' + CAST((CONVERT(varchar(10), @itemCount)
- 1) as varchar(12)) + char(09) + 'Nbr. of Unique FT Words = '
+  CONVERT(varchar(10), @keyCount) + char(09)
SET NOCOUNT OFF
END
go

-- Confirm FT population, 1 row should be returned (pub_id = 0736). 
SELECT pub_id, pr_info FROM pub_info WHERE CONTAINS(pr_info, 'moon')
go

-- Stop the Microsoft Search service on both servers
exec master..xp_cmdshell 'net stop "Microsoft Search"'
go

Run the following SQL Server script on the SOURCE server (server_1):

use pubs
go
-- Map a Drive letter to the destination server.
exec master..xp_cmdshell 'NET USE K: \\server_2\[drive]$'
go
-- Copy the destination server's FT catalog folder and files as a backup.
exec master..xp_cmdshell 'ROBOCOPY K:\MSSQL70\FTDATA\SQL0000500005
K:\MSSQL70\BACKUP\SQL0000500005 /E /NP'
go
-- Remove the destination server's full-text catalog folder and files.
exec master..xp_cmdshell 'RMDIR /S /Q K:\MSSQL70\FTDATA\SQL0000500005'
go
-- Copy the SOURCE full-text catalog folder and files to the destination server's
NEW full-text catalog location.
exec master..xp_cmdshell 'ROBOCOPY D:\MSSQL70\FTDATA\SQL0000500005
K:\FTData\SQL0000500005 /E /NP'
go
-- Restart the Microsoft Search service.
exec master..xp_cmdshell 'net start "Microsoft Search"'
go
-- Remove the mapped drive letter to the destination server, for example
-- K:\.
exec master..xp_cmdshell 'NET USE K: /delete'
go

You must run the following SQL script on the destination server (server_2):

use master
go
-- Enable system table updates.
sp_configure allow,1
go
reconfigure with override
go

use pubs
go
-- Record full-text catalog information (Note: path = NULL)
select * from sysfulltextcatalogs
go
-- Update the full-text catalog information with the new full-text catalog location
UPDATE sysfulltextcatalogs set path = 'E:\FTData'
   WHERE ftcatid = 5
go
-- Record full-text catalog info. (Note: path = E:\FTData)
select * from sysfulltextcatalogs
go

use master
go
-- Disable system table updates.
sp_configure allow,0
go
reconfigure with override
go

-- CAUTION: Back up your registry hive before you contine!

-- Search and replace HKLM "Gather" registry keys with new full-text catalog
-- folder location [10 row(s) affected]:
exec master..xp_cmdshell 'REGFIND -m \\server_2 -p
\Registry\Machine\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer\SQL0000500005
"E:\MSSQL70\FTDATA\SQL0000500005" -r "E:\FTData\SQL0000500005"'
go
-- Search and replace HKLM "Gatherer Manager" registry keys with new FT
-- catalog folder location [6 row(s) affected]:
exec master..xp_cmdshell 'REGFIND -m \\server_2 -p
"\Registry\Machine\SOFTWARE\Microsoft\Search\1.0\Gathering
Manager\Applications\SQLServer\Projects\SQL0000500005"
"E:\MSSQL70\FTDATA\SQL0000500005" -r "E:\FTData\SQL0000500005"'
go
-- Search and replace HKLM indexer registry keys with new FT catalog folder
-- location [6 row(s) affected]:
exec master..xp_cmdshell 'REGFIND -m \\server_2 -p
"\Registry\Machine\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer\SQL0000500005"
"E:\MSSQL70\FTDATA\SQL0000500005" -r "E:\FTData\SQL0000500005"'
go

-- Restart the Microsoft Search service
exec master..xp_cmdshell 'net start "Microsoft Search"'
go

-- Confirm FT population, 1 row should be returned (pub_id = 0736). 
use pubs
go
SELECT pub_id, pr_info FROM pub_info WHERE CONTAINS(pr_info, 'moon')
go

You can "stop" a full-text population in progress by running the following SQL Server code:

Caution If the item count is not equal to the number of rows that full-text indexed plus one additional, the full-text catalog size may be be incorrect!

-- Run the following code after starting full crawl/population through another connection.
use pubs
go
BEGIN
SET NOCOUNT ON
EXEC sp_fulltext_catalog 'PubInfo', 'stop'

-- Wait for crawl to stop

--  NOTE: For larger tables, increase the WAITFOR DELAY time appropriately

DECLARE @status int, @itemCount int, @keyCount int, @indexSize int
SELECT @status = FullTextCatalogProperty('PubInfo', 'populatestatus')
WHILE (@status <> 0)
BEGIN
  WAITFOR DELAY '00:00:01' -- wait for 1 second before checking FT
                           -- Populatestatus...
  SELECT @status = FullTextCatalogProperty('PubInfo', 'populatestatus')
END
WAITFOR DELAY '00:00:05' -- wait for 5 seconds to receive correct FT Property
                         -- info (add more time for larger tables)...
SET @itemCount = FullTextCatalogProperty('PubInfo', 'itemcount')
SET @keyCount = FullTextCatalogProperty('PubInfo', 'uniquekeycount')
SET @indexSize = FullTextCatalogProperty('PubInfo', 'indexsize')
PRINT 'Nbr. of Rows FT Indexed = ' + CAST((CONVERT(varchar(10), @itemCount) - 1)
as varchar(12)) + char(09) + 'Nbr. of Unique FT Words = ' +
CONVERT(varchar(10), @keyCount) + char(09)
SET NOCOUNT OFF
END
go
-- Can return: Nbr. of Rows FT Indexed = -1 Nbr. of Unique FT Words = 0 
-- (depending upon when the crawl/population stopped)

Clean-up procedures

Run the following SQL script on both servers:

Drop the full-text index, full-text catalog, and then disable full-text search in the pubs database:

use pubs
go
sp_fulltext_table 'pub_info', 'drop'
go
sp_fulltext_catalog 'PubInfo', 'drop'
go
sp_fulltext_database 'disable'
go
sp_fulltext_service 'clean_up'
go
-- end SQL Script !

REFERENCES

For more information about how to obtain the dbid for a database, see the "DB_ID" topic in SQL Server Books Online.

To determine if a database is full-text enabled, see the "DATABASEPROPERTY" or "IsFulltextEnabled" topics in SQL Server Books Online.

For more information about ftcatid, see the "sp_help_fulltext_catalogs", "OBJECTPROPERTY", or"TableFulltextCatalogId" topics in SQL Server Books Online.

Additional query words: FullText Full Text Fuzzy Search MSSearch Crawl

Keywords: kbinfo KB240867

Microsoft KB Archive/240867

Contents