Introduction to FileStream in SQL Server 2008 _mssql2008

Many friends do not know FileStream this function. Because FileStream is typically not enabled by default during installation, in SQL Server 2008, FileStream is introduced to varbinary large, unstructured data (such as text documents, images, and videos), etc. (max) is stored in the file system. Use the backup Restore feature of the database to back up the data together.

In the Select Database file path that window, there is a label "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

Copy Code code as follows:

EXEC sp_configure filestream_access_level, 2
Reconfigure

3. Create a database that supports FileStream

Copy Code code as follows:

[SQL]
CREATE DATABASE Test
On
(NAME = Test_dat,
FILENAME = ' C:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\data\testdat.mdf '),
FILEGROUP testGroup1 CONTAINS FILESTREAM
(NAME = Testgroup_dat,
FILENAME = ' C:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\data\testgroup1.ndf ')
LOG on
(NAME = Sales_log,
FILENAME = ' C:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\data\testlog.ldf ');

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

Copy Code code as follows:

[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 Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\data\testfilestreamrecord.ndf '
）
To FILEGROUP Filestreamrecord
Go

4. Create a data table that can store FileStream

Copy Code code as follows:

CREATE TABLE filestreamrecording
[SQL]
（
ID int,
Rowguidcolumn uniqueidentifier
Not NULL UNIQUE ROWGUIDCOL,
Filestreamcolumn varbinary (MAX) FILESTREAM
）；
[SQL]
[SQL]

5. Use

Copy Code code as follows:

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