MSSQL2005: Restore database without restoring full text catalog

Question

When restoring my database i have a problem with the physical file of the full text catalog being in use.

The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\MyCatalog' cannot be overwritten.  It is being used by database 'demo2'.

I use this restore statement

 RESTORE database demo from disk = N'c:\temp\demo.bak' WITH REPLACE 
,MOVE 'demo_Data' TO 'd:\Program Files\Microsoft SQL Server 2005\MSSQL\Data\demo.MDF'     
,MOVE 'demo_Log' TO 'd:\Program Files\Microsoft SQL Server 2005\MSSQL\Data\demo.LDF';

A solution would be to restore without the full text catalog, but i can's figure out how to do that.

Answer 1

My problem is I am restoring to a different server and the file path to the full text catalog that was on the other server does not exist on this server. Hence the back-up fails. need to know how to restore without the full text catalogue

Roger

Answer 2

I'm not sure how to restore without fulltext file but you can always restore with move. You are doing it already for database and log file, just complete your statement with:

,MOVE 'sysft_demo_ft' TO 'd:\NewLocationForFullTextFile.ft'

You can get the exact names of the files with:

RESTORE FILELISTONLY
FROM DISK='C:\yourbackupfile.bak'

Answer 3

I have had to restore databases with full-text as well (in fact, fixed my auto-restore script to auto-restore full-text index as well)

I don't know any way to NOT restore them (SQL restore will give you an error if you don't specify RESTORE WITH MOVE for the full-text index, unless there is a path that matches the original path inside the .bak file)

I suggest

1. restore the entire database, including full-text index
2. delete all full-text index inside the database enumerate all full-text catalogs SELECT name FROM DB.sys.fulltext_catalogs (SQL2005/2008) then generate the drop command (you may have to drop the index on the tables before dropping the catalogs) DROP FULLTEXT CATALOG [name]