Add a FILESTREAM feature to an existing SQL D/B

SQL Server 2008 allows to store binary data, if Database has a FILESTREAM feature

-00- If you Running SQL Express on Windows XP, you have to change service user login account.  If you do not do this, you will not be able to add FILESTREAM file to your database.
Use SQL Configuration manager, select your D/B instance,  Properties and change login account from Network Service to Local System.

-01-  Enable FILESTREAM feature
Use “SQL Server Configuration Manager”.
Under SQL Server Services select your instance and go to Properties.
Under FILESTREAM tab, click on Enable FILESTRAEM for Transact-SOL access.
If you are on Server 2008 and if you installed SQL Server Service Pack you are in trouble.
As far as I know, you would not be able to use FILESTREAM feature due to this error (see picture)
User article here to http://www.domainwebcenter.com/?p=45 to uninstall SQL Server 2008 SP 2.

Run this query:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Note:  Here is explanation of all three filestream_access_level (s):
0 : Disables instance FILESTREAM support
1 : Enables FILESTREAM TSQL support
2 : Enables FILESTREAM TSQL and streaming

-02- Add FILESTREAM to an existing D/B
ALTER database TimeAndExpense
ADD FILEGROUP TE_FG_Attachments
GO

-03-  Add a file  to a FILESTREAM group
ALTER DATABASE TimeAndExpense
ADD FILE
(
NAME= ‘TE_Attachments’,
FILENAME = ‘C:\Inetpub\Database\TE_Attachments’
)
TO FILEGROUP TE_FG_Attachments
GO

Note that FILEGROUP and FILENAME must have different names!  Otherwise you will get an error message – Access Denied.

Check with http://www.mssqltips.com/tip.asp?tip=1489 on how to manipulate these BLOB records from C#.

How to Move SQL Database with a FILESTREAM

You can use graphical interface to drop, move and and re-attach the SQL database with FILESTREAM feature.   Actually the first two steps are working just fine (drop and move),  but when you try to re-attach the database after moving it, SQL Manager complains that it cant find FILESTREAM file at its old location.

To overcome this, you need to use a Query command that will take care of all three files at the same time:

 CREATE DATABASE YourDB ON PRIMARY ( NAME = YourDB, FILENAME = 'P:\_SQL\YourDB\YourDB.mdf'), FILEGROUP FS1 CONTAINS FILESTREAM( NAME = FC_TE, FILENAME = 'P:\_SQL\YourDB\YourDB.Attachments') LOG ON ( NAME = YourDB_Log, FILENAME = 'P:\_SQL\YourDB\YourDB.ldf') FOR ATTACH GO 

Just adjust 3 FILENAME pathes to your needs, and use your naming conventions for FILEGROUP (FS1) and FILESTREAM NAME (FC_TE). if you do not know, where your Attachments are located, you can run this query to find out:

 USE YourDB GO SELECT type_desc, name, physical_name from sys.database_files 

…