I have a database that was restored from a SQL 2000 instance to a SQL 2005 instance some time ago. The SQL 2000 instance had old, unused Full Text Searches defined.
我有一个数据库,从一段时间以前从SQL 2000实例恢复到SQL 2005实例。 SQL 2000实例定义了旧的,未使用的全文搜索。
It appears that, when the file was restored, the full text searches were not restored but just deleted.
看来,当文件恢复时,全文搜索没有恢复,只是被删除了。
This left the database in a funny state, with the FTS files still associated with the database but the files the database knows about listed as OFFLINE. Which, it turns out, keeps the database from having a full backup done.
这使数据库处于一个有趣的状态,FTS文件仍然与数据库相关联,但数据库知道的文件列为OFFLINE。事实证明,这使数据库无法完成完整备份。
So, does anyone know how to remove the files from the database? ALTER DATABASE REMOVE FILE returns
那么,有没有人知道如何从数据库中删除文件? ALTER DATABASE REMOVE FILE返回
Msg 5009, Level 16, State 2, Line 1 One or more files listed in the statement could not be found or could not be initialized.
消息5009,级别16,状态2,行1无法找到或无法初始化语句中列出的一个或多个文件。
The actual backup command throws one of the following errors for each file that doesn't exist:
实际的备份命令会为每个不存在的文件抛出以下错误之一:
Msg 9987, Level 16, State 1, Line 1 The backup of full-text catalog '' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
消息9987,级别16,状态1,行1不允许备份全文目录'',因为它不在线。检查错误日志文件以了解全文目录脱机并使其联机。或者可以使用FILEGROUP或FILE子句将BACKUP限制为仅包括在线数据。
Anyone know how to fix this problem?
有谁知道如何解决这个问题?
3 个解决方案
#1
The answer to this conundrum turns out to be very simple, at least if you don't care about your FTS indices, which I don't.
这个难题的答案结果非常简单,至少如果你不关心你的FTS指数,我不这么认为。
EXEC master.dbo.sp_detach_db @dbname = N'dbname', @keepfulltextindexfile=N'false'
The key here is the second flag, @keepfulltextindexfile, which defaults to true. By setting that to false and then reattaching the database, all FTS data is dropped and backups can work the way they should.
这里的关键是第二个标志,@ keepfulltextindexfile,默认为true。通过将其设置为false然后重新连接数据库,将删除所有FTS数据,并且备份可以按照应有的方式工作。
#2
I fixed the report issue as follows
我修改了报告问题如下
- Put the database into single user mode.
- Detach the database. But before clicking OK for the detach, UNCHECK the Keep full text catalogs.
- Reattach the database
将数据库置于单用户模式。
分离数据库。但在单击“确定”以进行分离之前,请检查“保留全文目录”。
重新附加数据库
#3
You should have repopulated your full text catalogs. By default SQL Server 2005 does not do this to give the installation process a boost.
您应该重新填充全文目录。默认情况下,SQL Server 2005不会执行此操作以提升安装过程。
#1
The answer to this conundrum turns out to be very simple, at least if you don't care about your FTS indices, which I don't.
这个难题的答案结果非常简单,至少如果你不关心你的FTS指数,我不这么认为。
EXEC master.dbo.sp_detach_db @dbname = N'dbname', @keepfulltextindexfile=N'false'
The key here is the second flag, @keepfulltextindexfile, which defaults to true. By setting that to false and then reattaching the database, all FTS data is dropped and backups can work the way they should.
这里的关键是第二个标志,@ keepfulltextindexfile,默认为true。通过将其设置为false然后重新连接数据库,将删除所有FTS数据,并且备份可以按照应有的方式工作。
#2
I fixed the report issue as follows
我修改了报告问题如下
- Put the database into single user mode.
- Detach the database. But before clicking OK for the detach, UNCHECK the Keep full text catalogs.
- Reattach the database
将数据库置于单用户模式。
分离数据库。但在单击“确定”以进行分离之前,请检查“保留全文目录”。
重新附加数据库
#3
You should have repopulated your full text catalogs. By default SQL Server 2005 does not do this to give the installation process a boost.
您应该重新填充全文目录。默认情况下,SQL Server 2005不会执行此操作以提升安装过程。