sqlserver2008 创建支持文件流的数据库-优快云博客

本文详细介绍了如何在SQL Server中启用并配置FILESTREAM特性，包括如何通过SQL Server Configuration Manager启用特性，创建包含FILESTREAM数据的数据库和数据表，解决了在执行创建数据库SQL语句时可能出现的操作系统拒绝访问问题，并提供了不同操作系统的热补丁链接。

第一步打开sqlserver数据库filestream特性

How to: Enable FILESTREAM（12个步骤）

Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine. This topic describes how to enable FILESTREAM by using SQL Server Configuration Manager.

To enable and change FILESTREAM settings

On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point toConfiguration Tools, and then click SQL Server Configuration Manager.
In the list of services, right-click SQL Server Services, and then click Open.
In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
Right-click the instance, and then click Properties.
In the SQL Server Properties dialog box, click the FILESTREAM tab.
Select the Enable FILESTREAM for Transact-SQL access check box.
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.
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.
Click Apply.
In SQL Server Management Studio, click New Query to display the Query Editor.

In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Click Execute.

第二步执行创建数据库的sql语句

（在执行中可能会遇到系统拒绝访问的问题，如果没有问题那最好了，解决问题这块就不用看了

解决此问题请看这里http://connect.microsoft.com/SQLServer/feedback/details/435855/operating-system-error-2147024891-0x80070005-access-is-denied

注意他给了不同操作系统的热补丁，选择适合自己系统的。

这个是windows server 2003的 http://support.microsoft.com/kb/973573.

这个是 xp的http://support.microsoft.com/?id=978835）

- 创建数据库
Use Master
Go
--数据库文件路径（如果有多级文件夹，需要先手动预先建立才行）。
--数据库名称：EGDB
--数据库文件名称：EGDB_Data.mdf
--数据日志文件名称：EGDB_Data_Log.ldf
--文件流数据名称：EGDB_FileStreamData
--文件组名称：EGDB_FileGroup
--文件流名称：EGDB_FileStream
--
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'EGDB_HZZ')
DROP DATABASE EGDB_HZZ
GO

USE Master
GO
CREATE DATABASE EGDB_HZZ ON PRIMARY
( NAME = EGDB_HZZ_Data,
FILENAME = N'D:\db\SQL_EGDB\EGDB_HZZ_Data.mdf',
SIZE = 10MB,
FILEGROWTH = 10MB),
FILEGROUP EGDB_HZZ_FileGroup
( NAME = EGDBFileStream_Data,
FILENAME = N'D:\db\SQL_EGDB\EGDB_Data.ndf',
SIZE = 10MB,
FILEGROWTH = 5MB),
FILEGROUP EGDB_HZZ_FileStream CONTAINS FILESTREAM
( NAME = EGDB_HZZ_FileStream,
FILENAME = N'D:\db\SQL_EGDB\EGDB_FileStreamData')
LOG ON
( NAME = EGDB_HZZ_Data_Log,
FILENAME = N'D:\db\SQL_EGDB\EGDB_Data_Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 5MB);
GO

第三步创建数据表

use EGDB_HZZ
if exists (select 1
            from sysobjects
           where id = object_id('dbo.SYS_FILES')
            and   type = 'U')
   drop table dbo.SYS_FILES
go

/*==============================================================*/
/* Table: SYS_FILES                                             */
/*==============================================================*/
create table dbo.SYS_FILES (
   FILE_ID              uniqueidentifier     not null default newid(),
   FILE_NAME            varchar(100)         null default ' ',
   FILE_CONTENT_B       varbinary(max)       NULL,
   FILE_CONTENT_C       varchar(max)         null default ' ',
   FILE_SIZE            float                null,
   FILE_TYPE            varchar(50)          null default ' ',
   FILE_SERVER_PATH     varchar(200)         null default ' ',
   FILE_REMARK          varchar(300)         null default ' ',
   FILE_SYS_TIME        datetime             null default getdate(),
   constraint PK_SYS_FILES primary key (FILE_ID)
)