Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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
share|improve this answer
    
Kin: thanks for your answer, appreciate it. One last doubt I still have is, the Text search catalog has a bunch of files in a folder called FTData, now I'm not sure how to attach those files. But I do agree backup and restore sounds like a better option. But we were thinking if we could avoid that as we have bunch of databases and it could take a while to do backup restores instead of detach/attach. –  WhoIsNinja May 9 '13 at 19:22
    
thank you for appending the answer to my question in previous comment! –  WhoIsNinja May 10 '13 at 21:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.