Database Migration breaks WSS/SQL Server Full Text Search


So here’s a little-known issue with SharePoint and Full Text Search:

On Joel Oleson’s blog (if you don’t know who this guy is and you’re in SharePoint Operations, find out quickly. Aside from Bill English [the man, his blog], he’s the other Man in SharePoint Managment/Operations – both of these guys regularly present at TechEd conventions), I found a blog entry about how, if your Full Text Search (that’s the one that works in Windows SharePoint Services, and is provided through the back-end SQL Server) isn’t working, and you migrated the database from a different server, the reason would be, possibly/probably, that you migrated the database from a different server.

Yes, I mean either with SPSBackup.exe (which should be your friend by now if you’ve been doing a lot of this and you want to keep the SharePoint Portal Search Index between migrations) or normal SQL Backup/Restore and file operations.

So anyway, there are a couple of stored procedures in the blog you should use to restore your Full Text Search. As far as I can tell, these are either SQL standard stored procedures, or, more likely, SharePoint-installed stored procedures.

Also, rather like part of my previous blog entry, the fix is essentially “turn it off, turn it on”, with some curve-balls in there if things don’t go as expected. It turned out to be thorough enough for us, so maybe it’ll be thorough enough for you.

Since I had to puzzle the blog entry out a little bit, I’ll write what I understood of how to do this here.

  1. Mr. Oleson recommends restarting your SQL Services, but we didn’t find this completely necessary.
  2. Run the following stored procedure: exec proc_DisableFullTextSearch
    1. If you get an error about there not being a Full Text Catalog, then run the following in SQL Query Analyzer and start Step 2 again:

      USE [databasename]
      sp_fulltext_database enable

      Where [databasename] is the name of the _SITE database you’re having the issue with. [But don’t actually type the [] brackets in there or your geek compatriots will laugh at you.]

  3. Run the following stored procedure: exec proc_EnableFullTextSearch

So that should be it. The procedure, as I said, is mostly just turn off, turn on again.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.