如何从 SQL Server 2005 数据库中删除脱机文件 [英] How do I remove offline Files from a SQL Server 2005 database

问题描述

我有一个数据库，前段时间从 SQL 2000 实例恢复到 SQL 2005 实例.SQL 2000 实例定义了旧的、未使用的全文搜索.

看来，当文件被恢复时，全文搜索并没有恢复，而是被删除了.

这让数据库处于一种有趣的状态，FTS 文件仍然与数据库相关联，但数据库知道的文件被列为离线.事实证明，这会阻止数据库完成完整备份.

那么，有谁知道如何从数据库中删除文件?ALTER DATABASE REMOVE FILE 返回

<块引用>

消息 5009，级别 16，状态 2，第 1 行找不到或无法初始化语句中列出的一个或多个文件.

实际的备份命令会为每个不存在的文件抛出以下错误之一:

<块引用>

消息 9987，级别 16，状态 1，第 1 行不允许备份全文目录 ''，因为它不在线.检查错误日志文件，因为全文目录脱机并使其联机.或者，可以使用 FILEGROUP 或 FILE 子句来执行 BACKUP，以将选择限制为仅包括联机数据.

有人知道如何解决这个问题吗?

解决方案

事实证明，这个难题的答案非常简单，至少如果您不关心 FTS 指数，我不关心.

>

EXEC master.dbo.sp_detach_db @dbname = N'dbname', @keepfulltextindexfile=N'false'

这里的关键是第二个标志，@keepfulltextindexfile，默认为 true.通过将其设置为 false 然后重新附加数据库，所有 FTS 数据都将被删除，备份可以按应有的方式工作.

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.

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.

So, does anyone know how to remove the files from the database? ALTER DATABASE REMOVE FILE returns

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.

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.

Anyone know how to fix this problem?

解决方案

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.

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.

这篇关于如何从 SQL Server 2005 数据库中删除脱机文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！