{"id":4968,"date":"2019-02-20T15:36:36","date_gmt":"2019-02-20T23:36:36","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4968"},"modified":"2019-03-12T17:05:56","modified_gmt":"2019-03-13T00:05:56","slug":"the-curious-case-of-copying-filestream-files-between-tables","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-copying-filestream-files-between-tables\/","title":{"rendered":"The Curious Case of… copying FILESTREAM files between tables"},"content":{"rendered":"

(The Curious Case of…<\/em> used to be part of our bi-weekly newsletter<\/a>\u00a0but we decided to make it a\u00a0regular 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.)<\/p>\n

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

The simple answer is no – you can’t use that method for copying FILESTREAM<\/em> files between tables.<\/p>\n

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

So, simply copying the files from one directory to another wouldn’t work for two reasons:<\/p>\n