Enable FileStream in SQL Server

Recently in the study of the database to store large data files, see the FileStream this function, recorded for later use

FileStream generally in the installation of the default is not enabled, if you pay attention to, in the selection of the database file path that window, there is a label is "FileStream".

If you are not enabled during installation, you can use the following settings to turn on the FileStream feature.

1. Open SQL Server Configuration Manager, locate the SQL Server service you want to enable under SQL Server service (the default instance is typically MSSQLServer), right-click the service, select Properties, and in the Properties window you can see the FileStream tab, select Transact-SQL access enable FILESTREAM "

2. Execute the following command at SSMs

EXEC sp_configure filestream_access_level, 2

Reconfigure

3. Create a database that supports FileStream,

[SQL]

CREATE DATABASE Test

On

(NAME = Test_dat,

FILENAME = ' C:Program filesmicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAtestdat.mdf '),

FILEGROUP testGroup1 CONTAINS FILESTREAM

(NAME = Testgroup_dat,

FILENAME = ' C:Program filesmicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAtestGroup1.ndf ')

LOG on

(NAME = Sales_log,

FILENAME = ' C:Program filesmicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAtestlog.ldf ');

Of course, you can also use the following script to add a filegroup to the existing database to support FileStream.

[SQL]

ALTER Database Test

ADD FILEGROUP Filestreamrecord

CONTAINS FILESTREAM

Go

--add a file for storing database photos to FILEGROUP

ALTER Database Test

ADD FILE

(

Name= ' Filestreamrecord ',

FILENAME = ' D:program filesmicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATATestFileStreamRecord.ndf '

)

To FILEGROUP Filestreamrecord

Go

4. Create a data table that can store FileStream

CREATE TABLE filestreamrecording

[SQL]

(

ID int,

Rowguidcolumn uniqueidentifier

Not NULL UNIQUE ROWGUIDCOL,

Filestreamcolumn varbinary (MAX) FILESTREAM

);

[SQL]

[SQL]

5. Use

[SQL]

INSERT into Filestreamrecording VALUES (1, NEWID (), 0x00);

INSERT into Filestreamrecording VALUES (2, NEWID (), 0x00);

INSERT into Filestreamrecording VALUES (3, NEWID (), 0x00);

Go

SELECT filestreamcolumn.pathname () as ' PathName ' from filestreamrecording;

Refer to online Help or online Help for specific use.