Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a SQL Server 2008 database, with a VARCHAR(MAX) FILESTREAM column. I can load a record into it using this syntax:

`INSERT INTO DocumentRepository(DocumentExtension, DocumentName, Document) 
 'doc' AS DocumentExtension
 , 'Hamlet.doc' AS DocumentName
   AS Document;`

This loads my word document just fine. What I'd like to know is how to extract the data back to disk using SQL Server supplied tools. Might be T-SQL, bcp, etc, don't really care I would just like to know a way to do this without resorting to having to write a .NET application.

I've experimented with bcp, using the -c, -n, -N, and -w switches and it will extract the document but it's not formatted correctly. When I open it in Word there is a lot of binary gibberish at the beginning, and while the text is there all formatting is gone.


share|improve this question
You'll probably want to take this over to – squillman May 17 '09 at 22:00
agreed. SO is the correct site, for this question. – Pure.Krome May 17 '09 at 23:39
I've come to the conclusion that UndertheFold is correct, there is no way to do it without resorting to .Net. Been searching for several days and have found no satisfactory answer. SSIS may let you but I have not explored it (yet) one way or the other. – user2867 May 19 '09 at 19:10
up vote 1 down vote accepted

Great question - Basically you can't - you could select the path and access the file directly from the file system

But from Books online you will notice the operations are

  • insert
  • update
  • query
  • back up

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

share|improve this answer

I feel a bit embarrassed to admit this, given that I wrote the FILESTREAM whitepaper for Microsoft - but I don't know the answer. My feeling is the same as UndertheFold's - you can't do it. I had a poke about to see if I could find any way of doing it with SSIS but I couldn't.

share|improve this answer
+1 for honesty! :) – squillman May 18 '09 at 2:22

As UndertheFold states, you can't.

However, it is important to realize that renaming or deleting files directly in the filesystem will corrupt your db (Source). I'm not sure about editing the files, but personally I wouldn't risk it.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.