How to enable FILESTREAM from the command line.

Tuesday, June 03, 2008

The FILESTREAM feature in SQL 2008 is off by default. It must be enabled by a box administrator either during instance setup or post-setup using the SQL Configuration Manager UI.

The VBScript below allows you to enable FILESTREAM for a particular instance post-setup from the command line. The caller must be box admin to run the script.

Also note, that once a box administrator has enabled the feature on the box, the SQL admin must connect to the instance and configure the feature using sp_configure.

More details are at: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/03/filestream-configuration-and-setup-changes-in-sql-server-2008-february-ctp.aspx

Download the script by clicking on the Releases tab, then clicking on EnableFilestream.zip.

Usage

cscript filestream_enable.vbs [/Machine:MachineName] [/Instance:InstanceName] [/Level:level] [/Share:ShareName]

Parameters:

Machine: Target machine name. If not specified, local machine is the default.

Instance: Target instance. If not specified, default instance (MSSQLSERVER) is default.

Level: Level of FILESTREAM functionality to enable, a integer between 0 and 3. If not specified, level 3 is default.
0 - FILESTREAM functionality disabled.
1 - Enable FILESTREAM for T-SQL access.
2 - Enable FILESTREAM for T-SQL and file I/O streaming access but do not allow remote clients file I/O streaming access.
3 - Enable FILESTREAM for T-SQL and file I/O streaming access and allow remote client streaming access.

Share: Name of windows share that is created for file I/O streaming access to FILESTREAM data. If not specified, instance name is default.


Examples:

cscript filestream_enable.vbs

This will enable FILESTREAM on the local machine for the default instance MSSQLSERVER. The feature will be enabled for T-SQL and file I/O streaming access and the windows share MSSQLSERVER will be created.


cscript filestream_enable.vbs /Machine:. /Instance:SQLEXPRESS /Level:2 /Share:SQLEXPRESSLOCAL

This will enable FILESTREAM on the local machine for the instance SQLEXPRESS. The feature will be enabled for T-SQL and file/IO streaming access. Remote client file I/O streaming access is disabled. The windows share SQLEXPRESSLOCAL will be created.

Last edited Jun 4, 2008 at 8:59 PM by bonniefe, version 7

Comments

braveson Sep 9 at 11:15 AM 
If you are using SQL Server 2012, then edit file filestream_enable.vbs
Locate string \root\Microsoft\SqlServer\ComputerManagement10:FilestreamSettings and replace with \root\Microsoft\SqlServer\ComputerManagement11:FilestreamSettings

If you check, I have just updated 10 with 11. This worked for me :)

TonyBromo Mar 2, 2012 at 4:07 PM 
I am attempting to enable the FILESTREAM, I reinstalled SQL server 2008r2 which for some reason doesn't recognize my dev laptop as 64-bit windows OS (sys summary info below) and installs under WOW for 64-bit version of SQL 2K8r2. Could this be a BIOS update issue? (I can't think of anything else)... Help/suggestions?

===================
OS Name: Microsoft Windows 7 Enterprise
OS Version: 6.1.7600 N/A Build 7600
OS Manufacturer: Microsoft Corporation
OS Configuration: Member Workstation
OS Build Type: Multiprocessor Free
System Manufacturer: Dell Inc.
System Model: Latitude E6500
System Type: x64-based PC
Processor(s): 1 Processor(s) Installed.
[01]: Intel64 Family 6 Model 23 Stepping 10 GenuineIntel ~2801 Mhz
BIOS Version: Dell Inc. A18, 10/30/2009

gjenglish Jun 23, 2010 at 3:55 AM 
If you're looking for a manage code (C#, VB.NET) way to doing this, check-out this link:
http://stackoverflow.com/questions/2942469/is-there-an-alternative-of-using-a-vbscript-for-enabling-filestream-after-sql-ser

You can use the ManagementObject class as outlined in the above link.

ahsp May 29, 2009 at 12:46 PM 
Does anybody know how to enable Filestream from C# using WMI or something else.
I´m trying to do this in C# using WMI (basicly I translated the filestream_enable.vbs script to C#) but it doen´t work.
When I call InvokeMethod an exception is thrown.
Here is the code summery:

ManagementPath path = new ManagementPath();
path.Server = machine;
path.NamespacePath = @"\root\Microsoft\SqlServer\ComputerManagement10";
path.RelativePath = string.Format(@"FilestreamSettings='{0}'", instance);

ManagementObject mo = new ManagementObject(path);

mo.InvokeMethod("EnableFilestream", new object[] { 3, media });

vivf May 14, 2009 at 10:46 AM 
Can I have some help please? This is the output I am receiving when I run on both Vista and Windows 7 (7100). Yes I am running it from an elevated cmd line.

C:\>cscript filestream_enable.vbs /Machine:VIV-PC /Instance:SQLEXPRESS2008 /Level:1
Microsoft (R) Windows Script Host Version 5.8 (vista says 5.7)
Copyright (C) Microsoft Corporation. All rights reserved.

Machine: VIV-PC
Instance: SQLEXPRESS2008
Level: 1
ShareName:

Current Filestream configuration:

C:\filestream_enable.vbs(37, 1) (null): 0x8004100C

billva Sep 15, 2008 at 8:27 PM 
I've seen more than one site that shows
Level: Level of FILESTREAM functionality to enable, a integer between 0 and 3. If not specified, level 3 is default.
0 - FILESTREAM functionality disabled.
1 - Enable FILESTREAM for T-SQL access.
2 - Enable FILESTREAM for T-SQL and file I/O streaming access but do not allow remote clients file I/O streaming access.
3 - Enable FILESTREAM for T-SQL and file I/O streaming access and allow remote client streaming access.

but option 3 is not shown in the BOL doc or supported by the SP.
Comments? I also saw a comment on a MS blog (open for output only) that said the sp_configure filestream_access_level is going to be "removed" from the product. Comments?

ReedMe Jul 15, 2008 at 10:16 PM 
Mark, I've also sent you a private email. However, the best way to manage FILESTREAM settings with RC0 is with SQL Server Configuration Manager. Do you have more than one instance of RC0 installed? If the share exists in Computer Management from a previous installation, you can delete it there and try again.

MarkGiesen Jul 14, 2008 at 9:10 PM 
Doesn't work on my WinXP machine. Below is what I get. There seams to be an old Sharename known and that can't be changed. Ideas?

C:\Temp>cscript filestream_enable.vbs /Machine:. /Instance:SQL2008Eval /Level:2
/Share:Filestreams
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

Machine: .
Instance: SQL2008Eval
Level: 2
ShareName: Filestreams

Current Filestream configuration:

InstanceName = SQL2008Eval
AccessLevel = 0
ShareName = SQL2008EVAL
RsFxVersion = 0102

Calling method EnableFilestream with new level

Method failed: hr = -2147024713
HRESULT_FROM_WIN32 (ERROR_ALREADY_EXISTS) == IDS_FILESTREAM_DUP_SHARE_NAME (0x80
0700B7 : -2147024713)


New Filestream configuration:

InstanceName = SQL2008Eval
AccessLevel = 0
ShareName = SQL2008EVAL
RsFxVersion = 0102

ReedMe Jul 8, 2008 at 4:58 PM 
bfrasca, it may be necessary to restart the instance if FILESTREAM was not enabled after running the script with no errors.

bfrasca Jul 7, 2008 at 2:21 PM 
Doesn't work on Windows Server 2008. Even after running the script, when I try to restore the AdventureWorks2008 db it tells me that filestream isn't enabled.

lwclark Jun 13, 2008 at 5:02 PM 
Of course if you're running Vista, you'll need to run cscript from a "run as administrator" command line.