Get SQL named instance name via PowerShell

Question

I have a SQL Job that (among other things) manipulates some files in folders that are named after SQL Named Instances.

Here's the relevant code within the SQL Job:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, 
@step_name=N'Do Stuff', 
@step_id=1,

blah blah blah...

@os_run_priority=0, @subsystem=N'PowerShell', 
@command= $dir = "\\SomeNetworkLocation\" + $env:computername
#do stuff with $dir

So this part, $dir = "\\SomeNetworkLocation\" + $env:computername, grabs the computer name and makes the full path of my file location, which works great for most of my servers. The issue I'm having is some of my servers are Named Instances, so pulling the server name isn't good enough, as it only returns the first part of the Named Instance. I need it get $env:computername + '_' + $MyInstance somehow. The format of named instances on SQL are MyServer\MyInstance, if that helps...

Is there a way to pull the named of the instance this job exists on via PowerShell?

@alexherm yeah i've been playing around with that. Get-SqlInstance -ServerInstance "MyServer" errors with A parameter cannot be found that matches parameter name 'ServerInstance'. and Get-SqlInstance -Credential -ServerInstance "TECHPCJRF03" prompts me to login, but even with logging in fails with Verify that the target server is accessible and that the SQL Server Cloud Adapter service is running. It is accessible though. I'm logged in now! — coinbird
– coinbird, Commented Jul 17, 2019 at 17:08
PowerShell won't know the instance name because it would be running in the Computer context (hence why $env:computername works), and it won't know because you could have several named instances on a particular machine. You will have to pull the named instance from the SQL script and pass it as a parameter into the PowerShell job. — HAL9256
– HAL9256, Commented Jul 17, 2019 at 18:59
@HAL9256 That makes sense. Is there a way to pass in the result of SELECT @@servername to the PowerShell section of the job? The PowerShell is all contained within that @command I posted above. — coinbird
– coinbird, Commented Jul 17, 2019 at 20:21

HAL9256 · Accepted Answer · 2019-07-17 21:07:10Z

1

If you are using SQL Jobs, you can find the server instance by using a SQL Server Agent token to get the instance name, as well as the server name.

As a starting point, I am thinking you can get away with:

@os_run_priority=0, @subsystem=N'PowerShell', 
@command= $dir = "\\SomeNetworkLocation\" + $(ESCAPE_DQUOTE(A-SRV)) + "_" + $(ESCAPE_DQUOTE(INST))
#do stuff with $dir

I would personally set up a PowerShell script that takes in the parameters -Server and ServerInstance as the default instance would always be MSSQLSERVER, and you may need to add in a line or two to handle that.

answered Jul 17, 2019 at 21:07

HAL9256

13.7k1 gold badge39 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

coinbird Over a year ago

Awesome! Testing this out now.

Collectives™ on Stack Overflow

Get SQL named instance name via PowerShell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related