How to restore full-text indexes using SQL server backup

sp_help_fulltext_catalogs

Search for full-text index Directories

sp_help_fulltext_columns

Full-text index Column

sp_help_fulltext_tables

Full-text index table

 

Drop fulltext index on TableName

 

Delete full-text index directory drop fulltext catalog catalog_name

 

If full-text indexes are not available in SQL backup, you must delete the indexes before restoring them.

 

The details are as follows:

 

How to obtain the number of full-text indexes in the database. How to delete full-text indexes SQL server 2005

An exception occurred when I used the command to back up the database today:

Msg 3007, Level 16, State 1, Line 4
The backup of the file or filegroup "sysft_db_FullText" is not permitted because it is not online. BACKUP can be performed med by using the FILEGROUP or FILE clses to restrict the selection to include only online data.
Msg 3007, Level 16, State 1, Line 4
The backup of the file or filegroup "sysft_ccc" is not permitted because it is not online. BACKUP can be performed med by using the FILEGROUP or FILE clses to restrict the selection to include only online data.
Msg 3007, Level 16, State 1, Line 4
The backup of the file or filegroup "sysft_aaa" is not permitted because it is not online. BACKUP can be performed med by using the FILEGROUP or FILE clses to restrict the selection to include only online data.
Msg 3013, Level 16, State 1, Line 4
Backup database is terminating abnormally.

Db_FullText, ccc, and aaa are the original full-text index directories.

After the system is re-installed, the original full-text index is damaged. After you try the full-text index, you find that the support for Chinese is not good. The investigation result is generally much less than the actual result. So you don't need to use it anymore.

But I forgot to create a full-text index on that table. It took a lot of time to share with you:

-- Full-text index directory sp_help_fulltext_catalogs -- full-text index column sp_help_fulltext_columns -- full-text index table sp_help_fulltext_tables

　　

You can use sp_help_fulltext_catalogs to find the created full-text index directory. However, full-text index columns and index tables do not exist. Therefore, you only need to delete the full-text index directory.

Delete full-text directory:
Drop fulltext catalog directory name.

When the above three SQL results are empty, the full-text index is cleared. You can back up the data.

If the full-text INDEX is still present, you need to start the full-text INDEX service and then run drop fulltext index on the full-text INDEX table, and then run the drop fulltext catalog directory name. OK.

References:

14.8 2005 new: T-SQL statements related to full-text indexes

In versions earlier than SQL Server 2005, full-text directories and full-text indexes are created and managed using stored procedures. Some T-SQL statements related to full-text indexing have been added since SQL Server 2005 to create and manage full-text directories and full-text indexing.

14.8.1 create a full-text directory

The T-SQL statement for creating a full-text directory is:

 1 CREATE FULLTEXT CATALOG catalog_name 2  3      [ON FILEGROUP filegroup ] 4  5      [IN PATH 'rootpath'] 6  7      [WITH <catalog_option>] 8  9      [AS DEFAULT]10 11      [AUTHORIZATION owner_name ]12 13 <catalog_option>::=14 15      ACCENT_SENSITIVITY = {ON|OFF}

 

The parameters are described as follows:

L catalog_name: Full-text directory name. L on filegroup filegroup: name of the file group that contains the full-text directory. L in path 'rootpath': PATH of the full-text directory. L as default: Specify the full-text directory AS the DEFAULT directory. L AUTHORIZATION owner_name: Set the owner of the full-text directory to the Database User Name or role name. L accent_sensiti.pdf: Specifies whether the full-text index of the full-text directory is accent-sensitive.

 

 

Example 13: create a full-text directory named "TSQL full-text directory" in the Northwind database. The Code is as follows:

 

Create fulltext catalog tsql full-text directory on filegroup [PRIMARY] in path 'e: \ book \ SQL Server 2005 \ database \ chapter 14th \ database after running 'AS DEFAULT

 

 

14.8.2 modify full-text directory attributes

After creating a full file directory, if you find that its settings are unsatisfactory, you can use the T-SQL statement to modify it. The T-SQL statement code for changing full-text directory properties is as follows:

ALTER FULLTEXT CATALOG catalog_name{ REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ]| REORGANIZE| AS DEFAULT}

 

The parameters are described as follows:

L catalog_name: Full-text directory name l REBUILD: regenerate the full-text directory. L WITH accent_sensiti.pdf: sets whether the full-text index of the full-text directory is accent-sensitive. L REORGANIZE: Execute the master merge index to merge the smaller indexes created during the index process into a large index. L as default: Specify the full-text directory AS the DEFAULT directory.

 

Example 14: regenerate the TSQL full-text directory with the following code:

Alter fulltext catalog tsql full-text directory REBUILD

 

14.8.3 create a full-text index

With the full-text directory, you can create a full-text index in the full-text directory. A full-text directory can contain multiple full-text indexes, but a full-text index can only belong to one full-text directory.

Each data table can have only one full-text index. The code for creating a full-text index T-SQL statement is as follows:

 

CREATE FULLTEXT INDEX ON table_name     [(column_name [TYPE COLUMN type_column_name]          [LANGUAGE language_term] [,...n])]     KEY INDEX index_name          [ON fulltext_catalog_name]     [WITH          {CHANGE_TRACKING {MANUAL | AUTO | OFF [, NO POPULATION]}}     ]

 

 

The parameters are described as follows:

L table_name: name of the data table. L column_name: name of one or more columns included in the full-text index. Full-text indexes can only be performed for columns of the char, varchar, nchar, nvarchar, text, ntext, image, xml, and varbinary types. L type column type_column_name: name of the COLUMN in the data table that stores column_name document TYPE. L LANGUAGE language_term: LANGUAGE in which data is stored in column_name. L key index index_name: name of the field with only one-click INDEX in the data table. L ON fulltext_catalog_name: Full-text directory name. L MANUAL: Specifies whether to use the SQL Server proxy or manually disseminate trace logs. L AUTO: when the data in the associated data table is modified, SQL Server automatically updates the full-text index. L OFF [, no population]: The index data change list is not retained.

 

Example 15: create a full-text index for the "title", "content", and "file" fields in the "article" table. The Code is as follows:

Create fulltext index on Article (title, content, file type column extension) key index pk _ Article on tsql full-text directory

 

Note: Because the full-text index has been created for the "article" table in the previous chapter, you must delete the original full-text index before running this example.

 

14.8.4 modify full-text index attributes

After creating a full text index, if you find that its settings are unsatisfactory, you can use the T-SQL statement to modify it. The code for changing the full-text index property's T-SQL statement is as follows:

ALTER FULLTEXT INDEX ON table_name   { ENABLE   | DISABLE   | SET CHANGE_TRACKING { MANUAL | AUTO | OFF }   | ADD ( column_name     [ TYPE COLUMN type_column_name ]     [ LANGUAGE language_term ] [,...n] )     [ WITH NO POPULATION ]   | DROP ( column_name [,...n] )     [WITH NO POPULATION ]   | START { FULL | INCREMENTAL | UPDATE } POPULATION   | STOP POPULATION   }

 

Some parameters are described as follows:

L table_name: name of the data table. L ENABLE | DISABLE: ENABLE or DISABLE full-text indexing. L MANUAL: Specifies whether to use the SQL Server proxy or manually disseminate trace logs. L AUTO: when the data in the associated data table is modified, SQL Server automatically updates the full-text index. L OFF: The index data change list is not retained. L ADD: Specify to ADD a column to the full-text index. L with no population: specifies that the full-text index is not filled WITH the full-text index after the full-text index column is deleted. L DROP: Specifies to delete a column from the full-text index. L START ...... POPULATION: start to fill the full-text index. L FULL: Specify to fill all FULL-text indexes. L INCREMENTAL: Specify to fill the modified rows since the last filling. L UPDATE: Specify to process all inserts, updates, or deletions since the last UPDATE change tracking index. L stop population: STOP filling in progress.

 

Example 16: Disable the full-text index of the "article" table. The Code is as follows:

Alter fulltext index on Article DISABLE

 

Example 17: enable full-text indexing for the "article" table. The Code is as follows:

Alter fulltext index on Article ENABLE

 

Example 18: Delete the "file" field in the "article" table from the full-text index. The Code is as follows:

Alter fulltext index on Article DROP (file)

 

Example 19: Fill in the full-text index of the "article" table. The Code is as follows:

Alter fulltext index on article START FULL POPULATION

 

14.8.5 Delete full-text index

The syntax code for removing the T-SQL of a full-text index is simple, as shown below:

DROP FULLTEXT INDEX ON table_name

 

Example 20: Delete the full-text index in the "article" table. The Code is as follows:

Drop fulltext index on Article

 

14.8.6 Delete the full-text directory

The syntax code to delete the T-SQL of the full-text directory is simple, as shown below:

DROP FULLTEXT CATALOG catalog_name

 

Example 21: Delete the full-text index in the "article" table. The Code is as follows:

Drop fulltext catalog tsql full-text directory

Reference http://msdn.microsoft.com/zh-cn/library/ms142549 (v = SQL .105). aspx