Enable FileStreaming in SQL Server 2008

I was trying to restore a database to a SQL Server 2008, and I got this error

System.Data.SqlClient.SqlError: FILESTREAM feature is disabled. (Microsoft.SqlServer.Smo)

Was bit confused, and after digging it out I Found that under default configuration of SQL Server 2008, FILESTREAM feature is disabled. Microsoft always has these kind of SMART default configurations with new releases. If you can remember SQL Server 2005 came with disabled Remote Connections in the first release. Then they enable it with the built in SP1 setup release. Anyway this is how you can make it enable,

Using the following TSQL is the easiest way,

EXEC sp_configure 'filestream_access_level', '2'

0= Disabled (which is the default)
1= Enables FILESTREAM for Transact-SQL access
2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access

Also you can use SQL Server Configuration Manager to do this,

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
9. Click Apply.

This will sort out all of your problems with file handling which you get under default configuration of the SQL Server 2008. Anyway later I found that we can change this default configuration at the time of the installation.


Anonymous said...

Great article it surely save few minutes for me. Cheers

Blog Directory - OnToplist.com