| Groups | Blog | Home |
sql server full text search : Rebuild FT Index After Restore
Lots of questions... Perhaps you should read this KB article 240867 (Q240867) "INF: How to Move, Copy, and Backup Full-Text Catalog Folders and Files" at http://support.microsoft.com/default.aspx?scid=kb;EN-US;240867 first and if you plan your move correctly (same dbid on both servers, et al.), you should be able to use these procedures successfully. Assuming that you're using SQL Server 2000 with the latest service pack (a bug was fixed to enabled this), you can script out the FT metadata via the Enterprise Manager to be able to create the FT Indexes and FT Catalogs on your destination server, if the procedures in the KB article are too lengthy. Regards, John [quoted text, click to view] "Mark Weidner" <mjw22@drexel.edu> wrote in message news:#CAi92fuDHA.2432@TK2MSFTNGP10.phx.gbl... > Hello All, > > I've come across the need to backup and restore a DB w/ FT catalog to > another server. I would not have a problem running a full population, as it > is relatively small (takes < 1 hr to populate). The restored DB of course > contains the old FT Catalog entry with the old path. I've encountered > problems in the past where I could not simply delete the old catalog entry > and create a replacement. I would prefer not to do this, but I think the > easiest solution may be to simply delete the full-text catalog from the DB > before performing the backup, backup and do another restore (so that there > is no FT catalog entry when the DB is restored, however, this would mean I > would have to setup all the tables and columns for FT indexing again). > Recommendations? Is there a simple way to delete the reference to a FT > catalog that no longer exists? What is the recommended procedure for > recreating a FT index when the DB is moved to another server? > > Thanks! > > -Mark > >
You're welcome, Mark,
In the Enterprise Manager, click on the + next to the database folder and expand the folder. Right-mouse click on your FT-enabled source database and select "All Tasks" and click on "Generate SQL scripts". This will bring up a dialog box, click on the Options table and check mark "Script full-text indexes", then click on the General tab and click on "show all" and select only your FT enabled tables and click "add". Additionally, you can click on the Formatting tab and un-check "Generate the CREATE.." and "Generate the DROP...", as these may not be necessary to script out. Then click on Preview to review the T-SQL code and copy to the clipboard and to notepad for further editing. With careful editing, you can end up with a SQL script that will only generate the necessary T-SQL code to FT-enable the destination database, create the FT Catalog and setup the table and column specific info and activate the FT Catalog, as in the edited example below: -- edited if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1 exec sp_fulltext_database N'enable' GO if not exists (select * from dbo.sysfulltextcatalogs where name = N'FTSCatalog') exec sp_fulltext_catalog N'FTSCatalog', N'create' GO exec sp_fulltext_table N'[dbo].[FTSTable]', N'create', N'FTSCatalog', N'FTSTable_IDX' GO exec sp_fulltext_column N'[dbo].[FTSTable]', N'TextCol', N'add', 1033 GO exec sp_fulltext_column N'[dbo].[FTSTable]', N'ImageCol', N'add', 1033, N'ExtCol' GO exec sp_fulltext_table N'[dbo].[FTSTable]', N'activate' GO If you have any scheduled SQLServerAgent jobs to run Full or Incremental Populations, you can script these out as well or use the Full Population code example in the below KB article. Regards, John [quoted text, click to view] "Mark Weidner" <mjw22@drexel.edu> wrote in message news:#0ILmcguDHA.2308@TK2MSFTNGP09.phx.gbl... > Thanks John for the quick reply. Since the client will be performing these > tasks, I was indeed looking for a way that didn't involve modifying registry > keys. The destination server should indeed be the lastest SP for SQL Server > 2000. How would I go about scripting out the metadata via EM? > > Thanks! > > -Mark > > "John Kane" <jt-kane@comcast.net> wrote in message > news:uCpjf$fuDHA.3496@TK2MSFTNGP11.phx.gbl... > > Mark, > > Lots of questions... Perhaps you should read this KB article 240867 > > (Q240867) "INF: How to Move, Copy, and Backup Full-Text Catalog Folders > and > > Files" at http://support.microsoft.com/default.aspx?scid=kb;EN-US;240867 > > first and if you plan your move correctly (same dbid on both servers, et > > al.), you should be able to use these procedures successfully. > > > > Assuming that you're using SQL Server 2000 with the latest service pack (a > > bug was fixed to enabled this), you can script out the FT metadata via the > > Enterprise Manager to be able to create the FT Indexes and FT Catalogs on > > your destination server, if the procedures in the KB article are too > > lengthy. > > > > Regards, > > John > > > > > > > > > > "Mark Weidner" <mjw22@drexel.edu> wrote in message > > news:#CAi92fuDHA.2432@TK2MSFTNGP10.phx.gbl... > > > Hello All, > > > > > > I've come across the need to backup and restore a DB w/ FT catalog to > > > another server. I would not have a problem running a full population, > as > > it > > > is relatively small (takes < 1 hr to populate). The restored DB of > course > > > contains the old FT Catalog entry with the old path. I've encountered > > > problems in the past where I could not simply delete the old catalog > entry > > > and create a replacement. I would prefer not to do this, but I think > the > > > easiest solution may be to simply delete the full-text catalog from the > DB > > > before performing the backup, backup and do another restore (so that > there > > > is no FT catalog entry when the DB is restored, however, this would mean > I > > > would have to setup all the tables and columns for FT indexing again). > > > Recommendations? Is there a simple way to delete the reference to a FT > > > catalog that no longer exists? What is the recommended procedure for > > > recreating a FT index when the DB is moved to another server? > > > > > > Thanks! > > > > > > -Mark > > > > > > > > > > > >
Hello All,
I've come across the need to backup and restore a DB w/ FT catalog to another server. I would not have a problem running a full population, as it is relatively small (takes < 1 hr to populate). The restored DB of course contains the old FT Catalog entry with the old path. I've encountered problems in the past where I could not simply delete the old catalog entry and create a replacement. I would prefer not to do this, but I think the easiest solution may be to simply delete the full-text catalog from the DB before performing the backup, backup and do another restore (so that there is no FT catalog entry when the DB is restored, however, this would mean I would have to setup all the tables and columns for FT indexing again). Recommendations? Is there a simple way to delete the reference to a FT catalog that no longer exists? What is the recommended procedure for recreating a FT index when the DB is moved to another server? Thanks! -Mark
Thanks John for the quick reply. Since the client will be performing these
tasks, I was indeed looking for a way that didn't involve modifying registry keys. The destination server should indeed be the lastest SP for SQL Server 2000. How would I go about scripting out the metadata via EM? Thanks! -Mark [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:uCpjf$fuDHA.3496@TK2MSFTNGP11.phx.gbl... > Mark, > Lots of questions... Perhaps you should read this KB article 240867 > (Q240867) "INF: How to Move, Copy, and Backup Full-Text Catalog Folders and > Files" at http://support.microsoft.com/default.aspx?scid=kb;EN-US;240867 > first and if you plan your move correctly (same dbid on both servers, et > al.), you should be able to use these procedures successfully. > > Assuming that you're using SQL Server 2000 with the latest service pack (a > bug was fixed to enabled this), you can script out the FT metadata via the > Enterprise Manager to be able to create the FT Indexes and FT Catalogs on > your destination server, if the procedures in the KB article are too > lengthy. > > Regards, > John > > > > > "Mark Weidner" <mjw22@drexel.edu> wrote in message > news:#CAi92fuDHA.2432@TK2MSFTNGP10.phx.gbl... > > Hello All, > > > > I've come across the need to backup and restore a DB w/ FT catalog to > > another server. I would not have a problem running a full population, as > it > > is relatively small (takes < 1 hr to populate). The restored DB of course > > contains the old FT Catalog entry with the old path. I've encountered > > problems in the past where I could not simply delete the old catalog entry > > and create a replacement. I would prefer not to do this, but I think the > > easiest solution may be to simply delete the full-text catalog from the DB > > before performing the backup, backup and do another restore (so that there > > is no FT catalog entry when the DB is restored, however, this would mean I > > would have to setup all the tables and columns for FT indexing again). > > Recommendations? Is there a simple way to delete the reference to a FT > > catalog that no longer exists? What is the recommended procedure for > > recreating a FT index when the DB is moved to another server? > > > > Thanks! > > > > -Mark > > > > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |