I finally managed to create a snapshot from a database that contains filestream tables. Now I am stuck selecting records from snapshot. I have a simple table let's say( tblAttachments: ID, FileName, CreationTime , Value). When I want to select with a simple select query:

select * from tblAttachments

I receive:

Large object data (LOB) for table 'dbo.tblAttachments' resides on an offline filegroup ('extraBlobs') that cannot be accessed.

Is there anyway to use snapshot with filestream enabled dbs.

If snapshots are not usable with databases with filestream data, why sql server allows to take snapshots from them?

CodePudding user response：

You can use database snapshots with filestream enabled dbs.(although with some limitations)

for the sample you provided you can just ommit the blob column and select records from the table in snapshot:

select ID, FileName, CreationTime from tblAttachments

and you will get records without blob field. In many cases that suffic.