SQL Server 2012 Filestream : An error occurred during the drop table operation on a FileTable object

Question

I'm using SQL Server filestream feature in some databases to save profile pictures as files. I added new filegroup of type filestream and database file inside this filegroup. Also added a filetable and saved the files successfully and everything was fine.

For some reasons I have to remove the filestream from the database and the instance also.

I searched for the right steps to do that and found the following :

• drop all the filetables in the database using (Drop).
• Remove the Filesteam database files.
• Remove the Filestream filegroup.
• After that disable it at the instance level and the service.

The problem is when i started these steps (step one : Drop Table -FiletableName-) I got this error:

An error occurred during the drop table operation on a FileTable object. (HRESULT = ‘0x80070490’)

I searched the internet for a solution and could not find one, does any one have a clue?.

Answer 1

Finally I found the source of the problem to the above error.

I changed the [Filestream Directory Name] property to have a unique value and that removed the error and I was able to drop the table. That happened because I have restored another copy of the database with different database name in the same instance but did not change the [Filestream Directory Name] property.

You can find this property by right clicking the database, choosing properties, then choosing Options page, and under Filestream group of properties change [Filestream Directory Name] to have a unique value not used by other databases in the same instance.