全文檢索目錄和索引不是儲存在 SQL Server 資料庫中，而是個別儲存在由 Microsoft Search
Service 管理的檔案中。在進行 Microsoft SQL Server 復原期間，並不會復原全文檢索目錄檔案。此外，您無法使用 Transact
SQL 陳述式 BACKUP 和 RESTORE 來備份和還原全文檢索目錄檔案。執行復原或還原作業之後，您必須個別重新同步處理全文檢索目錄。只有
Microsoft Windows NT 系統管理員和 Microsoft Search Service
可以存取全文檢索目錄檔案。
雖然您無法使用 SQL Server
資料庫來備份全文檢索目錄，但是可以使用具有已啟用全文檢索搜尋的大型資料表的實際執行資料庫。如果您執行這項操作，可能必須移動、備份或還原這些全文檢索目錄和它們的實際執行資料庫。全文檢索目錄母體擴展或重新同步的執行時間可能會比一般維護時間還長。本文將告訴您可用來移動或複製目錄，而不需初始化完整母體擴展的各種方法。這個方法可避免發生嚴重停機的情形。
停止獨立電腦上的 Microsoft Search Service。對於叢集 SQL Server
組態，請讓全文檢索資源保持連線狀態。停止 Microsoft Search Service 之後，您就無法對資料庫執行使用述詞 CONTAINS 或
FREETEXT、資料列集值函數 CONTAINSTABLE 或 FREETEXTTABLE
的全文檢索查詢。此外，全文檢索搜尋查詢也無法運作，並且您會收到下列錯誤訊息：
注意 根據預設，全文檢索二進位碼檔案的位置為： SQL Server 7.0：\Mssql7\FTDATA Microsoft SQL Server 2000 預設執行個體：\Program Files\Microsoft SQL Server\MSSQL\FTDATA SQL Server 2000 具名執行個體：MSSQL$InstanceName\FTDATA
這些位置和資料夾路徑在不同的電腦上可能會有所不同。在這個情況中，您必須變更兩個登錄項目，才能在完成 server_2
上的還原之後，讓路徑指向全文檢索二進位碼檔案的正確位置。
下列 SQL Server 指令碼示範如何將全文檢索目錄資料夾從 SQL Server 7.0 (server_1)
移動或「移轉」到另一台執行 SQL Server 7.0 的電腦 (server_2)，即 server_2
上不同的本機磁碟機或路徑位置。本文假設具有全文檢索搜尋的 SQL Server 7.0 是安裝在 Windows NT 4.0
電腦上，並且兩部伺服器都具有相同的資料庫和資料表識別碼。
您必須以伺服器的本機
Administrators 群組成員的身分登入，並且必須是 SQL Server sysadmin 伺服器角色的成員 (或者以 sa 登入)。此外，您必須是資料庫的資料庫擁有者 (DBO)，才能執行下列 SQL Server
指令碼。
請在兩部伺服器上執行下列 SQL Server 指令碼：
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
您必須在目的地伺服器 (server_2) 上執行下列 SQL 指令碼：
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
您可以執行下列 SQL Server 程式碼，藉此停止進行中的全文檢索母體擴展：
警告 如果項目計數不等於全文檢索索引的資料列數目再加 1，表示全文檢索目錄大小可能不正確！
-- 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)
清除程序
請在兩部伺服器上執行下列 SQL 指令碼：
在 pubs 資料庫中卸除全文檢索索引、全文檢索目錄，然後停用全文檢索搜尋：
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 !
Windows Live
Facebook
Twitter
Linkedin
Digg it
Yahoo
Delicious
StumbleUpon
Yammer
Reddit
Technorati
FriendFeed
Email
回此頁最上方
