SQL Server 2005: Attach database using sp_attach_db with full text catalog

Question

Having issues attaching a Database and full text catalog from server A to server B with the "sp_attach_db" command in SQL Server 2005 SP3. All the database files (data, log, full-text catalog) have been copied from server A to server B. I am guessing that this is indeed possible and that my syntax is just incorrect, but I can't seem to find any examples or good documentation anywhere. Here is some pseudo syntax I am using...

exec sp_attach_db '<database_name>', '<database data file>', '<database full text catalog folder>', '<database log file>'

or somewhat of an example....

exec sp_attach_db 'database', 'C:\Database\Data\database_data.mdf', 'C:\Database\Data\FTData', 'C:\Databases\Logs\database_log.ldf'

I get an error of "Unable to open the physical file" from this command and the attach command fails. If I remove the full text catalog folder from the sp_attach_db command the database mounts but gives me a warning that it couldn't find the full text catalog. What am I missing!?

Solution

Use CREATE DATABASE ... FOR ATTACH;. See Example H:

Attaching a full-text catalog that has been moved
The following example attaches the full-text catalog AdvWksFtCat along with the AdventureWorks2008R2 data and log files. In this example, the full-text catalog is moved from its default location to a new location c:\myFTCatalogs. The data and log files remain in their default locations.

USE master;
GO
--Detach the AdventureWorks2008R2 database
sp_detach_db AdventureWorks2008R2;
GO
-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks2008R2 database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks2008R2 ON 
    (FILENAME = 'c:\...\Data\AdventureWorks2008R2_Data.mdf'), 
    (FILENAME = 'c:\...\Data\AdventureWorks2008R2_log.ldf'),
    (FILENAME = 'c:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO