How can I select all instances both default and named instances using set-location.
Default instances can be retrieved using the following code:
Set-Location SQLSERVER:\SQL$server\DEFAULT\DATABASES;
Named and default Instances are supposed to able to be retrieved using the following code:
Set-Location SQLSERVER:\SQL$server$SQLInstance\DATABASES;
However the set location for both named and default instances is not working correctly
If $server is the machine name = server1 and InstaceName - Server1 then
the Result of the set-location for default instances would be server1\server1. where it should be server1 only.
If $server is the machine name = server1 and InstaceName - Server1 then
the Result of the set-location for named instances would be server1\server1\test. where it should be server1\test only.
I am using the code below nut it is not working. What am I doing wrong/
Table Structure: Database and Table on a Central DBA management server
Machine InstanceName
Server1 Server1 --Default Instance
Server2 Server2\Test --NamedInstance
$Servers = invoke-sqlcmd -ServerInstance DBAServer -Database ‘MONITORDB2’ -Query “Select MachineName, InstanceName From Servers”
$InputFile = “D:\Scripts\srvr_db_tblspc3.sql”;
$Servers | ForEach-Object{
$server = “$($.MachineName)"
$SQLInstance = "$($.InstanceName)”
Set-Location SQLSERVER:\SQL$server$SQLInstance\DATABASES;
Get-ChildItem | ForEach-Object{
$Db = $_.Name;
Invoke-Sqlcmd -SuppressProviderContextWarning -InputFile $InputFile -Database $Db;