How to enable SQL Filestream using Powershell

Question

I'm spinning up a SQL server ready for deployment. Using AWS userdata, everything is being configured to this stage using PowerShell. However, the database needs to have Filestream enabled at level 2, but I can't find a way to do this using PowerShell or any other command-line utility.

The script I've written for this part is this:

Import-Module SQLPS -DisableNameChecking
$instanceName = $env:COMPUTERNAME
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$server.Configuration.FilestreamAccessLevel.ConfigValue = 2
$server.Alter()
Set-ExecutionPolicy RemoteSigned -force
Invoke-Sqlcmd "EXEC sp_configure filestream_access_level, 2"
Invoke-Sqlcmd "RECONFIGURE"
Get-Service -Name MSSQLSERVER | Restart-Service -force
$server.Properties | clip
$server.Configuration | clip
import-module SQLPS -DisableNameChecking

But it's not enabling Filestream.

Does anyone have any ideas on how I can get this working?

https://haydenhancock.wordpress.com/2013/03/12/enable-filestream-via-powershell/ — David Brabant, Jul 18 '17 at 11:19
Thanks David. That worked for me, once I changed the namespace to ComputerManagement13. — Dicky Moore, Jul 19 '17 at 08:12

score 1 · Answer 1 · answered Nov 21 '22 at 00:09

1

Easiest way is to use DBATOOLS.

Install-Module dbatools -Scope CurrentUser
Enable-DbaFilestream -SqlInstance $env:COMPUTERNAME

answered Nov 21 '22 at 00:09

PollusB

1,726
2
22
31

score 0 · Answer 2 · answered Aug 11 '22 at 09:57

Posting this as an answer in case the blog goes offline.

This worked for me for SQL Server 2019:

# Enable FILESTREAM
$instance = "MSSQLSERVER"
$wmi = Get-WmiObject -Namespace "ROOT\Microsoft\SqlServer\ComputerManagement15" -Class FilestreamSettings | where {$_.InstanceName -eq $instance}
$wmi.EnableFilestream(3, $instance)
Get-Service -Name $instance | Restart-Service
 
Set-ExecutionPolicy RemoteSigned
Import-Module "sqlps" -DisableNameChecking
Invoke-Sqlcmd "EXEC sp_configure filestream_access_level, 2"
Invoke-Sqlcmd "RECONFIGURE"

How to enable SQL Filestream using Powershell

2 Answers2