Backup and Restore Database - Affect on Full Text Indexes

Question

We are planning to move a database from SQL Server 2008 R2 to a SQL Server 2016 instance. This database has a few Full Text Catalogs, with all the indexes part of the PRIMARY file group.

When we perform a Full backup and restore on this database, will the full text catalog and indexes be included? Also, will the indexes begin automatically repopulating upon being restored on the new server or are they simply ready to go?

We performed a similar procedure several years ago and we seem to recall that as soon as a database was restored, the full text indexes began immediately repopulating. This was fine except for the fact that the indexes were very large and population took a long time to complete and even had some drag on server performance. Is a repopulation of the indexes required, and if so, is there a way to prevent them from immediately kicking off upon being restored (if that is in fact what may occur)?

Answer 1

Hi MarkSanchez-9060,

In SQL Server 2008 and later versions, a full-text catalog is a logical or virtual object that contains a group of full-text indexes. Therefore, a new full-text catalog is not treated as a database file with a physical path. Therefore, to back up the full-text catalog, you need to identify every filegroup that contains a full-text index of the catalog and back each of them up, one by one.
If a full-text indexed table was online and a population was running when the backup was created, the population is resumed after the restore.
Please refer to Upgrade Full-Text Search and Back Up and Restore Full-Text Catalogs and Indexes which might help.

Best Regards,

Amelia

Answer 2

But surely, if you backup the whole database, the FT data comes with it and no need to mess about with filegroup backups, right? I mean, the FT data and meta-data is just data, like any data in the database. That article referred to talks about if you want to backup only the full text data. I don't think that is the case for Mark...

Answer 3

My reply was mostly a remark for the other reply. I had a feeling that you aren't doing file or filegroup restore, so I just wanted to comment on Amelias reply - mostly not being relevant to your situation.

I have little experience with upgrading FT. But check out the documentation for the RESTORE command. Here is some text from that section (note the reference to sp_fulltext_service):

"After you restore an earlier version database to SQL Server 2019 (15.x), the database is automatically upgraded. Typically, the database becomes available immediately. However, if a SQL Server 2005 (9.x) database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. If the upgrade option is set to import (upgrade_option = 2) or rebuild (upgrade_option = 0), the full-text indexes will be unavailable during the upgrade. Depending on the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. Note also that when the upgrade option is set to import, the associated full-text indexes are rebuilt if a full-text catalog is not available. To change the setting of the upgrade_option server property, use sp_fulltext_service."

https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15

Answer 4

Anyway if we are talking for production environment you have to test it first.

The easier way to migrate without long downtime is to turn your db to full recovery model, backup , restore to the other system with norecovery and when the time is right just transfer all the transaction logs backups after backup time and restore them with the last one to be with recovery.