Sql-server – Full-text search migration 2005 -> 2012 (.ndf file?)

full-text-searchmigrationsql server

So I'm trying to figure out the details for migrating from SQL Server 2005 to SQL Server 2012.

  1. I tried to do restore and backup of a DB from 2005 to 2012. This worked well and for the text search catalog created a .ndf file.

  2. I tried to detach and attach a DB from 2005 to 2012, this also seemed to work fine including the text search but I'm wondering where's the catalog files on the file system because I don't see a .ndf file or a dedicated folder for text search catalog (the way we used to have in versions prior to 2008)?

(PS. I atttached only .mdf and .ldf files, not sure how to attach a specific text search catalog file because it's multiple files)

I might have missed something but any suggestions will be helpful, thanks!

Best Answer

There are couple of things to consider :

backup and restore is the safest option that you can use for migration from SQL 2005 to 2012.

Refer here for more details of what you are experiencing.

Full text has undergone major chance in 2008 and up.

from BOL,

Attaching a SQL Server 2005 Database to SQL Server 2012

In SQL Server 2008 and later versions, a full-text catalog is a logical concept that refers to a group of full-text indexes. The full-text catalog is a virtual object that does not belong to any filegroup. However, when you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2012 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005.

The state of each attached full-text catalog on SQL Server 2012 is the same as when the database was detached from SQL Server 2005. If any full-text index population was suspended by the detach operation, the population is resumed on SQL Server 2012, and the full-text index becomes available for full-text search.

If SQL Server 2012 cannot find a full-text catalog file or if the full-text file was moved during the attach operation without specifying a new location, the behavior depends on the selected full-text upgrade option. If the full-text upgrade option is Import or Rebuild, the attached full-text catalog is rebuilt. If the full-text upgrade option is Reset, the attached full-text catalog is reset.

EDIT:

You can attach the detached database along with the fulltext catalog using Create database ... FOR ATTACH.

-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks ON 
    (FILENAME = 'D:\...\Data\AdventureWorks_Data.mdf'), 
    (FILENAME = 'D:\...\log\AdventureWorks_log.ldf'),
    (FILENAME = 'D:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO
Related Question