how to set to NULL all the filestream varbinary(max) fields?

Question

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

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

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

What I tried to do is

Update FILE_REPOSITORY_TABLE
SET FILE = NULL

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

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.

How can I do?

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.

Answer 1

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.