How to enable Filestream in SQL SERVER 2008 and SQL Server 2012?

Filestream was introduced in SQL Server 2008 for the storage and management of unstructured data.

Follow the below steps to enable this filestream in SQL Server2008.Let us see one by one.

To enable filestream through SQL Server configuration manager:

1.Open SQL Server configuration manager.Open SQL Server services

2.Select the instance for which you want to enable Filestream.Right click the instance->properties.

3.In the SQL Server Properties dialog box, click the Filestream tab.

4.Select the Enable Filestream for Transact-SQL access.

5.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.

6.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.

7.Click Apply.

Enable Filestream access level server configuration option:

In SQL Server Management Studio, click New Query.Execute the below query

[0 -Disables FILESTREAM,1 -Enables FILESTREAM for T-SQL,2 -Enables FILESTREAM for T-SQL and Win32 streaming access]

Syntax:

EXEC sp_configure filestream_access_level,2

RECONFIGURE with override

Create filestream enabled database:

1. We can enable file stream while creating the database  (or) If the database is already created we can enable filestream using alter database.

To create file stream enable database you can use below query

CREATE DATABASE DBname
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

 

Enable filestream on existing database:

To enable file stream on existing database you can use alter database  command with  SET FILESTREAM similar to the example below or SSMS 

ALTER DATABASE [DBNAME] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’Directoryname’ ) WITH NO_WAIT
GO

 

Enable filestream for database using SQL Server 2008 Management Studio:

1. Connect to SQL Server Instance using SQL Server Management Studio

2. In the Object Explorer, right click the instance and select Properties.

3. On the left panel click on the Advanced tab, then click on the drop down list of Filestream Access Level and select Full access enabled option.

4. Click Ok to save the changes.