Provides information about the MSSQL instances and their database files
Get-MssqlInstances [-show] [<CommonParameters>]
The function returns various information for each local instance of the MSSQL server as
PSCustomObject. In addition, the Files property of each object is filled with the file properties of the instance. This includes the date of the last file modification as well as the size of the database.
The data is mainly accessed via the registry. In addition, the status values of the SQL service and the SQL browser are evaluated.
If the query is performed with administrative rights, it is also checked whether the firewall has opened the UDP port of the SQL browser and whether there is a firewall rule for the SQL server service.
The information is additionally displayed on the monitor via the Show parameter.
The advantage of the PSCustomObject data type over a hashtable is, for example, the simple access to the property names such as: $instances.PatchLevel as well as further processing with Format-Table and Format-List.
Example 1:Shows information about the MSSQL instances #
PS C:\> $instances= Get-MssqlInstances -show
# Output from within the function SERVER SETTINGS FOR: [ SQL2019 ] Instance : SQL2019 Version : 15.0.2000.5 PatchLevel : 15.0.4298.1 Collation : Latin1_General_CI_AS BinnPath : C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Binn DataPath : C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA BackupPath : C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup SqlService : Running BrowserService : Stopped NamedPipes : True TcpIp : True SharedMemory : True FirewallValid : False FirewallBrowser : False FirewallSqlServer : False DATABASE FILES FOR: [ SQL2019 ] Sizes are shown in MB MdfFile : Eulanda_Doe.mdf LdfFile : Eulanda_Doe_log.ldf MdfSize : 6387,0625 LdfSize : 565,4375 TotalSize : 6952,5 LastModified : 2023-03-06 04:42:21 MdfFile : Eulanda_John.mdf LdfFile : Eulanda_John_log.ldf MdfSize : 2692,75 LdfSize : 0,4921875 TotalSize : 2693,24 LastModified : 2023-03-06 04:37:31 Firewall rules was not to check, this requires administrator privileges. For this reason FirewallValid=False.
The result is stored in the
Instances variable. In addition, the Show parameter displays the most important values on the screen.
The function displays the most important data on the screen. Additionally it is displayed if the administrative rights are not sufficient to check the firewall rules.
Type: SwitchParameter Parameter Sets: (All) Aliases: Required: False Position: Named Default value: None Accept pipeline input: False Accept wildcard characters: False
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.