FILESTREAM feature is disabled

Question

I have a database set up with FILESTREAM enabled on it (if you go to Properties\Advanced on the instance, it shows that FILESTREAM is enabled). I also ran the sp_configure script on it after doing this to enable the access (to make sure it was enabled). But...as soon as I try to alter the database and add the filegroup to it, it says that the feature is disabled.

I have it enabled on other instances on the same computer, and those work fine. It is only this one that is not working. I have not been able to find any good suggestions as to why it is not working.

It is a SQL 2008 R2 instance.

Answer 1

I was unable to script this, (using the link provided by user1977061), as the MSSQLService requires a restart afterwards to apply the change. Even after doing this it didn't work, and instead I had to manually configure FILESTREAM usage via:

1. SqlServer Configuration Manager
2. Sql SQL Server Services
3. SQL Server(MSSQLSERVER) [Properties]
4. FILESTREAM tab - enable the first 2, and optionally 3rd check box.

Look at the SQLServer properties > Advanced > Running Values value for "Filestream Access Level" to ensure that the FILESTREAM is actually enabled if in doubt.