如何完成删除文件流和所有附加文件 [英] How to complete remove filestream and all attached files

问题描述

我已经尝试在本地数据库上尝试MSSQL(2008R2数据中心)的FILESTREAM功能.真实的数据库正在服务器上运行.我已经使用以下查询设置了整个FILESTREAM:

I have tried the FILESTREAM feature for MSSQL (2008R2 Data Center) on a local database, to experiment. The real database is running on a server. I have setup the whole FILESTREAM, using this query:

/* CREATE FILESTREAM AND FILESTREAM TABLE */
USE [master]
GO
ALTER DATABASE SenONew
ADD FILEGROUP [FileStream]
CONTAINS FILESTREAM
GO
ALTER DATABASE SenONew
ADD FILE
(
NAME = 'fsSenONew',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\SenONew.ndf'
)
TO FILEGROUP [FileStream]
GO

USE [SenONew]
GO
CREATE TABLE Filestore(
    FileID int PRIMARY KEY,
    RowID uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
    FileDescr nvarchar(max),
    FileIndex varbinary(max) FILESTREAM NULL)
GO

我正在尝试添加一些文件然后删除它们.

And I was experimenting with adding a few files then deleting them.

现在，由于这只是为了进行实验，因此我也想摆脱它.我正在使用本地服务器来开发将在真实服务器上使用的数据库，因此我在本地服务器上创建了BackUp，然后在真实服务器上还原了它，因此它得到了更新(软件正在开发中，因此数据库结构和数据都会发生很大变化，我需要对要测试软件的真实服务器进行完全还原.

Now since this was only meant to be an experiment, I also want to get rid of it. I'm using my local server for the development of the database that will be used on the real server, thus I'm creating BackUp's on my local server then Restore this on the real server, so it gets updated (software is in development, so the database structure changes much as well as the data and I need to do a full restore to the real server, where the software is being tested on).

经过数小时的搜索，我找不到任何关于我的问题的信息.

After hours of searching, I couldn't find anything on my problem.

我知道我需要:

1. 删除存储FILESTREAM信息的数据库表
2. 我需要删除FILESTREAM的文件
3. 删除文件组

因此，我正在使用此查询来摆脱我最初设置的所有内容:

So I'm using this query to get rid of everything I set up in the first place:

/* DROP FILESTREAM TABLE AND FILEGROUP */
USE SenONew
DROP TABLE Filestore
GO

ALTER DATABASE SenONew
REMOVE FILE fsSenONew

ALTER DATABASE SenONew
REMOVE FILEGROUP [FileStream]
GO

因此，我会尽一切努力，并且它也将正确无误地完成.因此，当我输入文件组，文件和文件位置时，我看到它们全部被删除了:

So I do everything as I should and it completes without error as well. So when I enter my filegroups, files and my file location, I see they are all completely removed:

但是当我对本地数据库(包括已删除的FILESTREAM，文件路径和文件组)进行备份并尝试使用它还原服务器时，会出现错误.

But when I do a BACKUP of my local database (which include the deleted FILESTREAM, file path and filegroup) and try to restore the server with it, I get errors.

创建备份的SQL:

/* CREATE BACKUP OF DATABASE WITHIN CURRECT CONNECTION */
DECLARE @FileName2 nvarchar(250)
SELECT @FileName2 = (SELECT 'C:\SenO BackUp\' + convert(nvarchar(200),GetDate(),112) + ' SenONew.BAK')
BACKUP DATABASE SenONew TO DISK=@FileName2
GO

然后在服务器上执行还原:

Then do the Restore on the server:

/* CREATE RESTORE OF DATABASE WITHIN REAL SERVER CONNECTION */
use master
alter database SenONew set offline with rollback immediate;

DECLARE @FileName2 nvarchar(250)
SELECT @FileName2 = (SELECT '' + convert(nvarchar(200),GetDate(),112) + ' SenONew.BAK')
RESTORE DATABASE SenONew
FROM DISK = @FileName2

alter database SenONew set online with rollback immediate;

我收到以下错误:

*(Msg 5121，第16级，状态2，第7行 "C:\ Program Files \ Microsoft SQL Server \ MSSQL10_50.SQLEXPRESS \ MSSQL \ DATA \ SenONew.ndf"指定的路径不在有效目录中.

*(Msg 5121, Level 16, State 2, Line 7 The path specified by "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\SenONew.ndf" is not in a valid directory.

消息3156，级别16，状态3，行7文件'fsSenONew'无法恢复到'C:\ Program Files \ Microsoft SQL Server \ MSSQL10_50.SQLEXPRESS \ MSSQL \ DATA \ SenONew.ndf'.使用WITH MOVE标识文件的有效位置.

Msg 3156, Level 16, State 3, Line 7 File 'fsSenONew' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\SenONew.ndf'. Use WITH MOVE to identify a valid location for the file.

消息3119，级别16，状态1，第7行，在计划RESTORE语句时发现了问题.以前的消息提供了详细信息.

Msg 3119, Level 16, State 1, Line 7 Problems were identified while planning for the RESTORE statement. Previous messages provide details.

消息3013，级别16，状态1，第7行RESTORE DATABASE正在异常终止. )*

Msg 3013, Level 16, State 1, Line 7 RESTORE DATABASE is terminating abnormally. )*

我删除了.ndf FILESTREAM位置，为什么它是指定的路径?另外，为什么fsSenONew尝试还原?我无法解决这个问题.内部是否有需要删除的路径?

I deleted the .ndf FILESTREAM location, why is it a specified path? Also, why is fsSenONew trying to restore? I can't get my head around it. Are there paths internally that I need to delete?

推荐答案

您可以检查:

SELECT * FROM SenONew.sys.data_spaces WHERE name = 'FileStream' 

它应该返回0行.

有一个过程可以从SQL Server 2008数据库中删除FILESTREAM功能:

There is a procedure to remove FILESTREAM features from a SQL Server 2008 database :

ALTER TABLE Filestore DROP column FileIndex
GO
ALTER TABLE Filestore SET (FILESTREAM_ON="NULL")
GO
ALTER Database SenONew REMOVE FILE fsSenONew
GO
ALTER Database SenONew REMOVE FILEGROUP [FileStream]
GO

如

as described in this article. But the steps you did should do the same thing.

您的问题当然很奇怪，但是我建议您尝试使用以下

Your problem is certainly strange, but I suggest that you try using following

USE SenONew
EXEC Sp_help 
EXEC Sp_helpfile 
EXEC Sp_helpfilegroup 

您可能会在其中找到可疑的东西，例如使用该FILEGROUP的另一个表.

You may find something suspicious there like another table using that FILEGROUP.

我已经完全按照您描述的步骤进行操作，无法重现您的问题.检查还原数据库"屏幕的外观.

I have done exactly the steps you describe and cannot reproduce your problem. Check how your Restore database screen looks like.

这篇关于如何完成删除文件流和所有附加文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！