Tutorial :Upgrade database from SQL Server 2000 to 2005 — and rebuild full-text indexes?



Question:

I'm loading a SQL Server 2000 database into my new SQL Server 2005 instance. As expected, the full-text catalogs don't come with it. How can I rebuild them?

Right-clicking my full text catalogs and hitting "rebuild indexes" just hangs for hours and hours without doing anything, so it doesn't appear to be that simple...


Solution:1

Try it using SQL.

Here's an example from Microsoft.

--Change to accent insensitive  USE AdventureWorks;  GO  ALTER FULLTEXT CATALOG ftCatalog   REBUILD WITH ACCENT_SENSITIVITY=OFF;  GO  -- Check Accentsensitivity  SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'accentsensitivity');  GO  --Returned 0, which means the catalog is not accent sensitive.  


Solution:2

Thanks, that helped because it showed what was wrong: My file paths were different. Here's how I fixed it:

1) Load database from SQL 2000 backup

2) Set compatibility mode to SQL 2005

USE mydb  GO    ALTER DATABASE mydb SET COMPATIBILITY_LEVEL = 90  GO  

3) Get the filegroup names

SELECT name     FROM sys.master_files mf    WHERE type = 4      AND EXISTS( SELECT *                    FROM sys.databases db                   WHERE db.database_id = mf.database_id                     AND name           = 'mydb')  

4) Then for each name (I did this in a little script)

ALTER DATABASE mydb   MODIFY FILE( NAME = {full text catalog name}, FILENAME="N:\ew\path\to\wherever")  

5) Then collect all the "readable" names of the catalogs:

SELECT name FROM sys.sysfulltextcatalogs  

6) Finally, now you can rebuild each one:

ALTER FULLTEXT CATALOG {full text catalog name} REBUILD  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »