Full Text Catalog Errors

Hi Naomi

Not sure about your first error, but the second one sounds like what we ran into doing automated restores to named instances on our test machine, which runs multiple named instances.

If you do a default parameters restore, SQLS tries to restore the full-text indexing files to the same location as they are in on the system that you're restoring from.

So when you try to restore the second instance, it tries to restore the  full-text indexing files over the top of the first one, which is in use, so you get that error. 

The solution is to move the file during the restore to the right  location.

Syntax looks like this (after setting the location in a string variable):

-- and RESTORE the database

database @dbname from disk = @backup_file

with replace

, recovery

, move 'sysft_Inventory_Search' to @ftdata_folder

We don't bother specifying the ftdata location in the create statement, as below - just moving them into the right place in the restore step seems to work fine.

EXECUTE

('CREATE DATABASE impresario

ON ( NAME = impresario_devData,FILENAME = '''

+ @data_file + ''',

SIZE = 80GB,

MAXSIZE = UNLIMITED ,

FILEGROWTH = 500MB )

LOG ON

( NAME = impresario_devLog,

+ @log_file + ''',

SIZE = 100MB,

MAXSIZE = UNLIMITED ,

FILEGROWTH = 50MB )'

1,

if you want to restore a database, you need to make sure nobody is connecting to it.

you can open the activity monitor and you can kill the process by right click it.

2,

if you restore a database with another copy (not its own backup), you need to tick the overwrite box through the option selection.

I will paste it in next post.

this forum only allow post one pic (and <64kb:-( )

have fun

1,

you can create a new database name it "impresario"

(if you already have a impresario database go to next step)

2,

right click the impresario database go to restore, then go to database, then choose "Device", pick up the db backup file, go back to the "options", select "Overwrite the existing database".

at this point, you can click the script dropdown list select "script action to new query window"

then the full script to do the restore work is there.

you can save it if you want to.( I never do this.)

3,

open the activity monitor check nobody is connecting to the impresario, ask them to logout.

4,

run the script, and wait.

In SQL view, restore database ends here.

5,

(For Tessitura application only,)

If you are doing the LIVE copy over to TEST, you need to do:

a,

run some script step up T_Default table.

b,

run the security check script,

c,

then run the dropping and create login in Tessitur Security tool.

 d, try to login again.

That is all.

:-):-):-)

Thanks Ken!

I moved the ftdata file with the script and it took. I restored from production which was great except every time I restore I receive this warning:

Warning: Wordbreaker, filter, or protocol handler used by catalog 'Inventory_Search' does not exist on this instance. Use sp_help_fulltext_catalog_components and sp_help_fulltext_system_components check for mismatching components. Rebuild catalog is recommended.

I rebuilt the catalog, backup the database, restored from that backup and do not receive this warning. However every time I restore from production I receive this warning. I don’t know how detrimental it will be if I do not rebuild the catalog each time because this is will be an automated process.

Thanks again for your help!

Naomi

1,

After a long time research, finally, I understood what happened on your machine.

you setup your "Full Text catalogs " in a custom way.

it is not in a default setup.

so you need to do quite a bit additional work to restore impresario database.

the solution is to redo the "Inventory_search", leave the "Catalog location" empty.

then you will not have this FTData crab again.

2,

If you want to keep the current setup,

you can run this to rebuild the "Inventory_Search".

USE

[impresario]

GO

REBUILD