如何将所有文件流varbinary(max)字段设置为NULL? [英] how to set to NULL all the filestream varbinary(max) fields?

问题描述

我需要从一个包含大量数据作为文件流数据的巨大数据库中创建一个测试数据库.

I need to create a test database out of a huge database where the most data is contained as filestream data.

我需要测试不与文件流相关的数据，所以我想做的是删除varbinary(max)信息.

I need to test not filestream related data, so what I'd like to do is to remove the varbinary(max) info.

这些是我的FILE_REPOSITORY_TABLE表中的字段:

These are the fields I have in my FILE_REPOSITORY_TABLE table:

[ID_FILE] [int] NOT NULL,
[FILE_DATA] [varbinary](max) FILESTREAM  NULL,
[GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL

我想做的是

Update FILE_REPOSITORY_TABLE
SET FILE = NULL

我原本希望它也删除文件，但是没有发生.

I was expecting this to delete the files too, but it didn't happen.

我无法删除记录，因为ID_FILE具有FK链接. (当从包含文件流数据的表中删除记录时，也会删除相关文件).无论如何，我也试图进行备份，但是文件很大，即使表中充满了NULL.

I cannot delete the records, since ID_FILE has FK links. (when deleting records from a table containing filestream data also the related files are deleted). I also tried to do a backup anyway but the file size was big, even if the table is full of NULLs.

我该怎么办?

最后说明:我的目标是拥有500MB的备份，而不是10GB的备份(我有9.5GB的文档).这仅用于测试目的.

Final note: my goal is to have a 500MB backup instead of a 10GB one (I have 9,5 GB of docs). THis is only for testing purposes.

推荐答案

文件数据通过垃圾回收过程进行回收，而不是立即删除. 相关博客文章，因此您可能需要强制执行检查点操作.

The file data is reclaimed through a garbage collection process, rather then being removed immediately. Related blog post, so you might need to force checkpointing to occur.

这篇关于如何将所有文件流varbinary(max)字段设置为NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！