[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.
For 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.
???: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.
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
????? ????? (server_1) ?? ?? SQL Server ????????? ?????:
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
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
-- 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)
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 !
????? ?? ???? ????
