INF: How to Move, Copy and Backup SQL 7.0 Full-Text Catalog Folders and Files

• Microsoft SQL Server, Standard Edition, version 7.0
• Microsoft SQL Server, Enterprise Edition, version 7.0

SUMMARY

Full-Text (FT) Catalogs and indexes are not stored in a SQL Server database. They are stored in separate files managed by the Microsoft Search service. The full-text catalog files are not recovered during a SQL Server recovery. They also cannot be backed up and restored using the T-SQL BACKUP and RESTORE statements. The Full-Text Catalogs should be resynchronized separately after a recovery or restore operation. The Full-Text Catalog files are accessible by the Microsoft Search service and the Windows NT system administrator.

While Full-Text Catalogs are not backed up with the SQL Server Databases, customers with SQL Server 7.0 Standard or Enterprise versions are now using Full-Text Search (FTS) enabled production databases with large tables. These customers have requirements for moving, backing-up and restoring these FT Catalogs along with their production databases. FT Catalog population or resynchronization run times can sometimes take longer than a typical "maintenance window" would allow.

This article documents several methods on how Full-Text Catalog folders and files can be copied, moved and backed-up within certain restrictions:

• Full-Text Catalog folders and files must be located on a local drive.



• Full-Text Catalog folders and files must retain their original names and folder contents.



• The Full-Text enabled Database Identifier (dbid) and Table Identifier (id) must be the same on both servers when moving or copying FT Catalogs between servers.

Backing up and Restoring FT Catalogs to the Same Server and Local Drive/Path

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.

Moving or Copying FT Catalogs between SQL Servers to the Same Local Drive/Path

1. Confirm that the master.dbo.sysdatabases dbid values for the FT enabled databases are the same on both servers.



2. Confirm that the [database_name].dbo.sysobjects id values for the FT enabled tables are the same on both servers. For more information about obtaining a table's id value, see SQL Server Books OnLine; topic: "object_id".



3. Confirm the FT Catalog name and local Drive/Path are the same on both servers through; sp_help_fulltext_catalogs 'FT_Catalog_Name'.



4. Stop the Microsoft Search Service on both servers through the SQL Server Service Manager, Control Panel's Services applet or through a DOS command prompt and type 'net stop "Microsoft Search"' with "Microsoft Search" in double quotes.



5. Map a drive letter on server_1 to server_2's corresponding drive or path as identified in step 3.



6. Copy the FT Catalog folder and files identified in step 3 from server_1 to server_2 through the DOS command XCOPY using the fully qualified FT Catalog drive or Path and folder name as the source location and the mapped FT Catalog drive or path and folder name as the destination location. Use XCOPY's switches /I /E to create all destination folders, if they do not exist on server_2.



7. If necessary, you can use the DOS command RMDIR /S /Q [FT_Catalog_drive/path] on server_1 to remove the FT Catalog files on server_1.



8. Re-start the Microsoft Search Service on both servers through SQL Server Service Manager, Control Panel's Services applet or through a DOS command prompt and type 'net start "Microsoft Search"' with "Microsoft Search" in double quotes.



9. Confirm that the new FT Catalogs folders and files are functional through a T-SQL SELECT statement using a CONTAINS or FREETEXT predicate in the WHERE clause.

Moving or Copying FT Catalogs between Local Drive/Paths on the Same SQL Server

1. Confirm the FT Catalog name and local drive or path through sp_help_fulltext_catalogs 'FT_Catalog_Name'. Record the Full-Text Catalog Drive letter and fully qualified Path for later use.



2. Stop the Microsoft Search Service through SQL Server Service Manager, Control Panel's Services applet or through a DOS command prompt and type 'net stop "Microsoft Search"' with "Microsoft Search" in double quotes.



3. Determine the new FT Catalog local drive or path.



4. Allow System Table updates through the system stored procedure sp_configure and RECONFIGURE with override. Then UPDATE the [database_name].dbo.sysfulltextcatalogs path column to the new local drive or path destination for the FT Catalog default folder, such as: d:\FTData.



5. Copy the FT Catalog folder and files identified in step 1 from server_1 to server_2 through the DOS command XCOPY. Use the fully qualified FT Catalog drive or path and folder name as the source location and the new FT Catalog drive or path as the destination location. Use XCOPY's switches /I /E to create all destination folders, if they do not exist.



6. Use the DOS command RMDIR /S /Q [FT_Catalog_drive/path] to remove the FT Catalog folders and files from the source location.



7. Use the Windows NT 4.0 Resource Kit's REGFIND Utility to find and replace the following HKLM Registry Key values for each 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] 

For more information on the syntax for using REGFIND Utility, go to a DOS command prompt and type 'REGFIND /?' without the single quotes. Also, be sure to use double quotes " " to surround the Registry Keys for both the -p and -r parameters. For example:



 

   -m \\server_2
   -p \...\"Old_FT_Catalog_Path_and_Folder"
   -r \....\"New_FT_Catalog_Path_and_Folder"
 

8. Re-start the Microsoft Search Service through the SQL Server Service Manager, Control Panel's Services applet or through a DOS command prompt and type 'net start "Microsoft Search"' with "Microsoft Search" in double quotes.



9. Disallow System Table updates through the system stored procedure sp_configure and RECONFIGURE with override.



10. Confirm that the new FT Catalogs folders and files are functional through a T-SQL SELECT statement using a CONTAINS or FREETEXT predicate in the WHERE clause.

http://www.microsoft.com/technet/

MORE INFORMATION

The following SQL script illustrates how to move or "migrate" a FT Catalog folder from SQL Server 7.0 (server_1) to another SQL Server 7.0 (server_2) to a different local drive or path location on server_2. It is assumed that SQL Server 7.0 with Full-Text Search is installed on Windows NT 4.0 Servers and that on both servers, the same database and table identifiers exist.

For the purposes of this illustration, the Pubs (dbid = 5) database and table pub_info (id = 645577338) will be Full-Text enabled and populated on both servers. For correct execution, replace server_2 with your true server name and replace the FT Catalog folder name and drive or path with your true FT Catalog folder name and drive or path.

You need to be logged on as a member of the server's local Administrators group, be a member of SQL Server's sysadmin server role (or logged on as 'sa') and be the Database owner (DBO) of the database to execute the following SQL scripts.

-- Execute the following SQL script on BOTH servers:

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

-- Creates and Activates the FT Catalog: PubInfo, if it does not exist.
-- Drops, Re-creates and Activates the FT 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 enabled and activated, Start Full Crawl/Population
BEGIN
SET NOCOUNT ON
EXEC sp_fulltext_catalog 'PubInfo', 'start_full'
--
--- Wait for crawl to complete
--  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 get 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 

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 FT Catalog folder and files.
exec master..xp_cmdshell 'RMDIR /S /Q K:\MSSQL70\FTDATA\SQL0000500005'
go
-- Copy the SOURCE FT Catalog folder and files to the destination server's NEW FT Catalog location.
exec master..xp_cmdshell 'ROBOCOPY D:\MSSQL70\FTDATA\SQL0000500005 K:\FTData\SQL0000500005 /E /NP'
go
-- Re-Start 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 

use master
go
-- Enable System Table UPDATEs
sp_configure allow,1
go
reconfigure with override
go

use pubs
go
-- Record FT Catalog information (Note: path = NULL)
select * from sysfulltextcatalogs
go
-- UPDATE the FT Catalog information with the NEW FT Catalog location
UPDATE sysfulltextcatalogs set path = 'E:\FTData'
   WHERE ftcatid = 5
go
-- Record FT Catalog info. (Note: path = E:\FTData)
select * from sysfulltextcatalogs
go

use master
go
-- Disenable System Table UPDATEs
sp_configure allow,0
go
reconfigure with override
go

-- CAUTION !! It is HIGHLY Recommended that you BACKUP your Registry Hive BEFORE proceeding !!

-- Search and Replace HKLM "Gather" Registry Keys with NEW FT 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

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

-- Execute 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 get 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 was stopped)
 

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

Additional query words: FullText Full Text Fuzzy Search MSSearch Crawl

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo