Activating and configuring SQL server 2008 FileStream

I have been using this feature for a while, but apparently a bug due to a Windows Update appeared, making this taks quite difficule.

I wanted to post my exact SCRIPT T-sql that worked fine (issued from a copy/paste, so no possible typo error - may be a HTML conversion can occur).

Here you go :
------

-- OBJECT Enable and Configure Filstream

-- AUTHOR : Vincent THAVONEKHAM, according to the MSDN post below
-- DATE : 2010.08.15
-- Docs :
-- Reference MSDN (Published: October 2008) :
http://msdn.microsoft.com/en-us/library/cc949109(SQL.100).aspx
--
-- Prerequisites : . Destroy any previous [documentsFileStream]
Database to run this script
-- . Add the AD accout NETWORK SERVICE with all access to the folder
-- D:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL

USE [master]

GO

-- Set the filestream_access_level configuration option

-- 0 – disable FILESTREAM support for this instance
-- 1 – enable FILESTREAM for Transact-SQL access only
-- 2 – enable FILESTREAM for Transact-SQL and Win32 streaming
access

-- (Transact-SQL and Win32 streaming access is
Enabled)

EXEC
sp_configure filestream_access_level,
2;

GO

RECONFIGURE;

GO

PRINT '==> SQL 2008 SHOULD REPLY :'

PRINT '==> L''option de configuration ''filestream
access level'' est passée de 2 à 2. Pour installer, exécutez l''instruction
RECONFIGURE.'

-- CREATION OF THE DATABASE FOR THE FILESTREAM

CREATE
DATABASE [documentsFileStream]
ON PRIMARY

( NAME = N'documentsFileStream',
FILENAME = N'D:\Program Files (x86)\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\documentsFileStream.mdf',

SIZE
= 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'documentsFileStream_log',
FILENAME = N'D:\Program Files (x86)\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\documentsFileStream_log.ldf',

SIZE= 1024KB , FILEGROWTH = 10%)

GO

ALTER
DATABASE [documentsFileStream]
SET COMPATIBILITY_LEVEL = 100

GO

ALTER
DATABASE [documentsFileStream]
SET ANSI_NULL_DEFAULT
OFF

GO

ALTER
DATABASE [documentsFileStream]
SET ANSI_NULLS OFF

GO

ALTER
DATABASE [documentsFileStream]
SET ANSI_PADDING
OFF

GO

ALTER
DATABASE [documentsFileStream]
SET ANSI_WARNINGS OFF

GO

ALTER
DATABASE [documentsFileStream]
SET ARITHABORT OFF

GO

ALTER
DATABASE [documentsFileStream]
SET AUTO_CLOSE OFF

GO

ALTER
DATABASE [documentsFileStream]
SET AUTO_CREATE_STATISTICS ON

GO

ALTER
DATABASE [documentsFileStream]
SET AUTO_SHRINK OFF

GO

ALTER
DATABASE [documentsFileStream]
SET AUTO_UPDATE_STATISTICS ON

GO

ALTER
DATABASE [documentsFileStream]
SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER
DATABASE [documentsFileStream]
SET CURSOR_DEFAULT  GLOBAL

GO

ALTER
DATABASE [documentsFileStream]
SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER
DATABASE [documentsFileStream]
SET NUMERIC_ROUNDABORT OFF

GO

ALTER
DATABASE [documentsFileStream]
SET QUOTED_IDENTIFIER OFF

GO

ALTER
DATABASE [documentsFileStream]
SET RECURSIVE_TRIGGERS OFF

GO

ALTER
DATABASE [documentsFileStream]
SET DISABLE_BROKER

GO

ALTER
DATABASE [documentsFileStream]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER
DATABASE [documentsFileStream]
SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER
DATABASE [documentsFileStream]
SET PARAMETERIZATION SIMPLE

GO

ALTER
DATABASE [documentsFileStream]
SET READ_WRITE

GO

ALTER
DATABASE [documentsFileStream]
SET RECOVERY FULL

GO

ALTER
DATABASE [documentsFileStream]
SET MULTI_USER

GO

ALTER
DATABASE [documentsFileStream]
SET PAGE_VERIFY CHECKSUM

GO

USE [documentsFileStream]

GO

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [documentsFileStream] MODIFY
FILEGROUP [PRIMARY]
DEFAULT

GO

PRINT '==> SQL 2008 SHOULD REPLY NOTHING; just the the query ran successfully'

-- ADD THE FILESTREAM PART : Create the FileGroup for FileStream

USE [documentsFileStream]

GO

ALTER
DATABASE [documentsFileStream]
ADD FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM;

GO

PRINT
'==> SQL 2008 SHOULD REPLY NOTHING; just the the query ran successfully'

-- Specify pathname of the directory that will be the root of the data container

-- NOTE: On your harddrive, DO NOT create the final folder FSDATA

-- SQL srv will automatically create it with the command below
-- Reminder : The folder D:\Program Files (x86)\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL
-- MUST have the AD account 'NETWORK SERVICE' with full access
-- else you will have the error:

-- CREATE FILE a rencontré l'erreur du système
d'exploitation 5(failed to retrieve text for this error. Reason: 15100) en
essayant d'ouvrir ou de créer le fichier physique 'D:\Program Files
(x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FSDATA'.

USE [documentsFileStream]

GO

ALTER
DATABASE [documentsFileStream]
ADD FILE (

NAME = FSGroup1File,
FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FSDATA')
TO FILEGROUP FileStreamGroup1;
GO

PRINT
'==> SQL 2008 SHOULD REPLY NOTHING; just the the query ran successfully'

-- The following query is just to ensure everything went OK on the creation
-- of the file group storage called FILESTREAM_GRP.

USE [documentsFileStream];

GO

SELECT

m.file_id,
m.type_desc,
g.name AS [filegroup_name],
m.name AS [file_name],
m.physical_name
FROM
sys.master_files m
INNER
JOIN sys.filegroups g
ON m.data_space_id = g.data_space_id
WHERE
database_id = DB_ID();
GO

PRINT
'==> SQL 2008 SHOULD show you 2 rows (at least). Check the T-SQL comments for more info'

-- YOU SHOULD HAVE 2 ROWS, such as :
-- 1 ROWS PRIMARY documentsFileStream D:\Program Files
(x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\documentsFileStream.mdf
-- 65537 FILESTREAM FileStreamGroup1 FSGroup1File D:\Program
Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FSDATA
--
-- moreover
--
-- in the folder D:\Program Files (x86)\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\FSDATA
-- you should have (if you have the right to see them), the
following folder and file respectively :
-- $FSLOG
-- filestream.hdr