SQLUNINTERRUPTED

Recently, I came across a question about validating if the SQL FileStream document has been modified outside of SQL or not.

My immediate reaction was one of shock. If a FileStream document has been modified outside of SQL, then we have bigger problems at hand. We have serious issues with SQL Server security and data consistency is at risk.

FileStream storage, is not supposed to be modified outside of SQL. Though we can have Win32 streaming of these documents(documents created on the File System), but these has to be done in the context of a SQL Transaction and only within the context of a SQL Transaction. FileStream was not created for external modifications.

If the documents are modified outside of SQL, then the database is risk. For example, if we by mistake delete the files from the file system, SQL Would treat it as a database corruption and would throw errors during the next Check DB run.

Msg 7904, Level 16, State 2, Line 1
Table error: Cannot find the FILESTREAM file "00000020-00000146-000a" for column ID 2 (column directory ID d35bf83a-99c0-4a7d-ac24-e9f7cf15a54b) in object ID 101575400, index ID 1, partition ID 72057594038910976, page ID (1:170), slot ID 0.
There are 4 rows in 1 pages for object "DocumentStore".
CHECKDB found 0 allocation errors and 3 consistency errors in table ‘DocumentStore’ (object ID 101575400).

But the question is, how do we figure out if a File Stream document was modified outside of SQL or not.

Short answer is there is NO way to do it. There is no inbuilt function or mechanism to figure that. The simple reason why this cannot be done is because the path to store the File Stream data is not exposed for Direct Access. Also the file names which are visible in the File System are not the same which SQL Server maintains. They are different and there is no way to correlate the SQL Names with the File system Name.

As a SQL Server DBA, you need to make sure that the File Stream files are not get modified outside of SQL.