CODEWITHSUNDEEP
sqlpowershellnamed-instance
coinbird
coinbird

Reputation: 1217

Get SQL named instance name via PowerShell

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?

Upvotes: 0

Views: 1955

Answers (1)

HAL9256
HAL9256

Reputation: 13493

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.

Upvotes: 1

Related Questions