WMI Provider for Configuration Management - Professional SQL Server 2008 Administration with Windows PowerShell - page 215

Table 9-4: FILESTREAM Access Level Values

Value Description

0 Disables FILESTREAM support for this instance

1 Enables FILESTREAM for Transact-SQL access

2 Enables FILESTREAM for Transact-SQL and local file system access

3 Enables FILESTREAM for Transact-SQL, local file system access, and

remote file system access

The ShareName parameter specifies the file share name that is used to enable local and remote clients to
obtain streaming access to FILESTREAM data through the file system. This value can be changed only
when the enabled state changes from 0 (disabled) or 1 (Transact-SQL only) to file system access (2 or 3).

In our example, FILESTREAM is disabled on the default instance. The following EnableFileStream.ps1
script enables FILESTREAM for Transact-SQL and local file system access. It uses the Get-WmiObject
cmdlet to instantiate a FILESTREAMSettings object associated with the default instance. Next, it invokes
the EnableFILESTREAM method of the object to enable FILESTREAM for Transact-SQL and local file
system access and set the share name to MSSQLSERVER. Then it confirms the changes by printing out
the access level and share name:

# . for the local computer
# If you want to connect to a remote machine, specify the machine name here.
$strComputer = "."
# Name of the targeted SQL Server instance. Here the default instance is targeted.
# For a named instance INSTANCE1, use "INSTANCE1".
$strInstanceName = "MSSQLSERVER"

$wmi = Get-WmiObject -computerName $strComputer -namespace
root\Microsoft\SqlServer\ComputerManagement10 `
-class FILESTREAMSettings -filter "InstanceName = '$strInstanceName'"

# Prints out the AccessLevel property before changing it.
Write-Host "The access level of FILESTREAM before the change is set to"
$wmi.AccessLevel ", and the file share name is " $wmi.ShareName

$wmi.EnableFILESTREAM(2, 'MSSQLSERVER') | Out-Null

$wmi = Get-WmiObject -computerName $strComputer -namespace
root\Microsoft\SqlServer\ComputerManagement10 `
-class FILESTREAMSettings -filter "InstanceName = '$strInstanceName'"

# Confirm the AccessLevel property has been set.
Write-Host "The access level of FILESTREAM after the change is set to"
$wmi.AccessLevel ", and the file share name is " $wmi.ShareName

Save the script in your script directory, C:\DBAScripts :

C:\DBAScripts\EnableFILESTREAM.ps1