Team,
I setup a separate SQL instance on a server to install the impresario database. I have run into two different errors.
1. I run:
CREATE DATABASE [Impresario]
ON
(NAME = Impresario_dat,
FILENAME = N'F:\MSSQL\DATA\Failover Data Files\impresario.mdf',
SIZE = 3MB)
LOG ON
(NAME = Impresario_log,
FILENAME = N'G:\MSSQL\LOGS\Failover Log Files\impresario.ldf',
SIZE = 2MB),
(FILENAME = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\FTData\Inventory_Search')
for Attach
I receive this warning:
Warning: Identity or last population complete time of full-text catalog in directory 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\FTData\Inventory_Search' does not match database 'Impresario'. The full-text catalog cannot be attached.
I move on to restore the database from production and receive this error:
The file "sysft_Inventory_Search" failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The error log states:
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'DeleteTree' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\Inventory_Search\MssearchCatalogDir' at 'fulltext.cpp'(1740).
I followed the steps again without attaching the full text catalog but again I cannot restore this database.
Has anyone else had this issue and have a solution?
Many thanks!
Naomi Williams
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 restore
-- and RESTORE the database
restore
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, FILENAME = ''' + @log_file + ''', SIZE = 100MB, MAXSIZE = UNLIMITED , FILEGROWTH = 50MB )' ); Ken
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 = '''
ON ( NAME = impresario_devData,FILENAME = '''
+ @data_file + ''', SIZE = 80GB, MAXSIZE = UNLIMITED , FILEGROWTH = 500MB ) LOG ON ( NAME = impresario_devLog, FILENAME = '''
SIZE = 80GB,
MAXSIZE = UNLIMITED ,
FILEGROWTH = 500MB )
( NAME = impresario_devLog,
FILENAME = '''
+ @log_file + ''',
SIZE = 100MB, MAXSIZE = UNLIMITED , FILEGROWTH = 50MB )'
FILEGROWTH = 50MB )'
);
Ken
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