Full-Text Catalog Organ Transplant: Moving SQL Server full text indexes

9 years ago
Eric Vanderburg

Full-text searching allows more complex searches such as word or phrase searching, inflection matching, proximity searching, and relevance matching to be performed on many different fields within tables.  In order for these searches to run SQL server builds and maintains a catalog.

We have such a large amount of data that rebuilding a full-text catalog can take as long as 14 days.  We needed a way to be able to bring the full-text catalog back without rebuilding it, essentially, I needed a backup.  Microsoft does not backup the full-text catalog in normal backup operations, and you cannot access it with software while the server is running because it is always in use.

You cannot use standard backup commands to back them up, and Microsoft does not have any utilities available for the task either.  They do, however, have a guide for how to migrate it from one server to another.  We needed to do just that.  I call the process full-text catalog organ transplant because organ transplants require the patient receiving the organ to have the same characteristics as the donor for the body to accept the new organ.

Full-text catalogs need to have a destination system with many of the same characteristics as their previous system, or they will not work at all.  The features that must be similar are the SQL server version, full-text catalog folders and file locations, full-text catalog folder and file names and contents, and the same database id and table ids on both servers and databases.

The first few similarities are easy to recreate on the destination system.  The database id is a little more complicated as some DBAs do not even know what the id is for their databases.  You can obtain the database id of a SQL server database by issuing the following command:

Select db_id()

You have to be in the database you want to query first.  Use databasename will accomplish this.

This database number is generated when the database is attached to the instance.  Since the numbers have to match between source and destination, you need to create a situation where the database will receive the same database id when attached to the target as it did at the source.

We found that you can reuse numbers from databases that have been detached, so the process involves knowing the database ids of the databases you will import and then the ones in use at the destination.

If there is a database using the needed id, simply detach it, attach the imported database, and then attach the database you just detached.  The imported database will assume the detached database’s id as long as it is the lowest available number.

If you have a gap in numbers such as in this example where there is no database with an id of 6 you will need to fill that gap first.  NOTE: master, tempdb, model, and msdb always have the numbers you see below.  You can do that easily by attaching the database with the id you want to use first so that it consumes that spot.

Database ID
master 1
tempdb 2
model 3
msdb 4
Database1 5
Database2 7
Database3 8
Database4 9

If you have some gaps and not enough databases to fill them, you can create new databases with no data in them to consume database ids.

The rest of the process can follow the guide outlined in Microsoft’s knowledge base article 240867, so I will not go into details on the procedure.  I hope this helps you if you are in the same situation.