The Curious Case of… copying FILESTREAM files between tables

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

I was asked last week whether it’s possible to create a table with a FILESTREAM column and then populate that column by copying FILESTREAM files from another directory in the FILESTREAM data container.

The simple answer is no – you can’t use that method for copying FILESTREAM files between tables.

In the data container, each table with a FILESTREAM column (or partition of a partitioned table with a FILESTREAM column) has a directory with a GUID name, and then there’s a sub-directory for each FILESTREAM column that also have GUID names. Then the FILESTREAM files themselves have filenames in the format 8hexdigits-8hexdigits-4hexdigits. The hex digits are the Log Sequence Number (LSN) of the log record that created the FILESTREAM file, and the data record contains that LSN as part of the in-row portion of the FILESTREAM column. (You can read a full explanation of the storage format in my post FILESTREAM directory structure.)

So, simply copying the files from one directory to another wouldn’t work for two reasons:

  • The FILESTREAM column in the data record of the target table wouldn’t be populated
  • And even if you created empty FILESTREAM columns, the LSN in the column wouldn’t match the LSN of the copied-in files

And you can’t use SELECT-INTO either, because it will not create FILESTREAM files and instead copies them and stores them in the new table as varbinary(max) with a limit of 2GB.

Bottom line: You can use INSERT INTO <target table> SELECT * FROM <source table> and that *will* populate the FILESTREAM files, but that means reading the FILESTREAM data through SQL Server’s buffer pool, which may be very inefficient for large amounts of data. So your best bet may be to write a little application that will using NTFS streaming access to directly copy the FILESTREAM data from the source table to the target table.

4 thoughts on “The Curious Case of… copying FILESTREAM files between tables

  1. Respected sir,

    In memory oltp also uses filestream but there we can restore database. Is something different between these 2

    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.