Managing Databases That Have Full-Text Enabled 1

[JohnBates]

hi experts,

We have a 2005 database that have the full-text feature enabled. I suppose that's necessary to be able to search columns that contain Text or Ntext data.

Anyway, is there any special care that's required - how you back it up, the catalog etc for a full-text database?

Thanks and Happy New Year to everyone! Many of you haved helped me a lot during 2007.

John

 

[mrdenny]

The SQL 2005 databases with full text the full text catalog is backed up along with the database when you issue the backup database command.

Do be careful when restoring to the same server, as it can cause you acidently overwrite the product full text catalog. SQL doesn't check if the catalog files are in use when the restore is started.

I've had some issues with backing up my SQL 2005 server with full text indexing. We have to stop the full text service before the backup can start. I do this via a second job which the backup job starts. It looks to see if the backup spid is being blocked by the MSSEARCH waittype. If it is then it stops and starts the service allowing the backup to complete.

This may be because of the large amount of load that we place on the tables which are full text enabled. I've used databases with much lower load on the full text tables which don't have this kind of problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog