How to set all fields varbinary (max) filestream to NULL?

Question

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

I need to check data not related to filters, so I would like to remove varbinary (max) information.

These are the fields that I have in the 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 expected this to delete the files too, but this did not happen.

I cannot delete entries since ID_FILE has FK links. (when deleting records from a table containing stream data, related files are also deleted). I also tried to backup, but the file size was large, even if the table is full NULL.

How can i do this?

Final note: my goal is to have a backup of 500 MB instead of 10 GB (I have 9.5 GB of documents). This is for testing purposes only.

Answer 1

File data is returned through the garbage collection process and then deleted immediately. A related blog post , so you may need to force a checkpoint.