SQL Server – Enable Filestream via Command Line Without Configuration Manager

this isn't exactly how filestream and filetables work, you can't specify the name of an existing share as SQL server will try and create the share that you specify in SQL Server Configuration Manager. This is because the share is not mapped directly to a folder on the filesystem like a normal share, but an NTFS data container that lives in the filestream filegroup that is created for the database.

There is a misconception with filestream/filetable that because the data is stored in the filesystem, it is just a normal folder, this isn't entirely correct.

When you create your database, you specify that one of the filegroups is a filestream filegroup, this isn't like a normal filegroup and is actually a series of NTFS folders called Data Containers, this is where your files actually get stored, you can't modify this folder directly, but you can look at the contents (not that there's much human readable stuff in here though, you can find your actual files in here though if you look hard enough through the GUID folders and oddly named files :)).

The file share that you use to copy the files into the table is actually a representation of the data inside these data containers that is presented to Windows explorer via a filter driver which uses the streaming APIs to make the changes to the data containers and add the rows to the filetable etc.

So if you want to specify the location that the files are physically stored, you need to specify that location as where you want the filestream filegroup stored, then you can change the share name to whatever you want.

Hopefully I can answer your question and explain how I did so.

To alter the service start mode using your code above you run

# Check Start Mode
$MyServerObject.StartMode
# Set Start Mode
$MyServerObject.StartMode ='Manual'
# Alter Start Mode
$MyServerObject.Alter()
# Check Start Mode again
$MyServerObject.StartMode

You can establish by using Get-Member as shown below

$MyServerObject|Get-Member

In the list of results you will see

StartMode Property Microsoft.SqlServer.Management.Smo.Wmi.ServiceStartMode StartMode {get;set;}

The {get;set;} shows that you can alter the property

You will also see

Alter Method void Alter(), void IAlterable.Alter()

This is the method that you will use to apply the change to the object.

You can use the same principle throughout your PowerShell use.

It is also possible to do this remotely, if you have the correct privileges for your account, to do the following

# Get Service                                         
$Service = Get-WmiObject win32_service -ComputerName COMPUTERNAME -Filter "Name = 'MSSQLSERVER'"            
# Get Start Mode                         
$Service.StartMode     
# Change Start Mode                                                                                                                
$Service.ChangeStartMode('Manual') 
#Check Start Mode                                                                                                                    
$Service.StartMode                                                                                                                     

to achieve the same result