Use Powershell To Get SQL Database Versions Locally or Remote

      No Comments on Use Powershell To Get SQL Database Versions Locally or Remote

Collecting version information for your environment is a never ending task but Powershell can be used to make things easier. Each SQL server instance can be different and a single server can have multiple SQL instances with different versions.

In SQL server we can use SSMS to run the following query and grab SQL version data as well as SQL server information too. We will use this query in Powershell to collect info on all of the SQL servers we know about.

SELECT 
	SERVERPROPERTY('MachineName') AS [ComputerName],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NetBios],
	SERVERPROPERTY('ServerName') AS [ServerName],
	SERVERPROPERTY('InstanceName') AS [Instance],
	SERVERPROPERTY('Edition') AS [Edition],
	SERVERPROPERTY('BuildClrVersion') AS [Build],
	-- SERVERPROPERTY('ProductBuild'),  SQL 2014 +
	SERVERPROPERTY('ProductLevel') AS [Level],
	SERVERPROPERTY('ProductUpdateLevel') AS [Update],
	SERVERPROPERTY('ProductVersion') AS [Version],
	SERVERPROPERTY('ResourceLastUpdateDateTime') AS [LastUpdate],
	SERVERPROPERTY('IsClustered') AS [Clustered], 
	SERVERPROPERTY('IsHadrEnabled') AS [AlwaysOn],
	SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IntegratedOnly],
-- 	SERVERPROPERTY('InstanceDefaultBackupPath'),  SQL 2019 +
	SERVERPROPERTY('IsSingleUser') AS [SingleUser],
	SERVERPROPERTY('InstanceDefaultDataPath') AS [DefaultDATA], 
	SERVERPROPERTY('InstanceDefaultLogPath') AS [DefaultLOG], 
	SERVERPROPERTY('Collation') AS [DefaultCollation]

Remote SQL Instances

To execute the above command we need to connect to each SQL instance, run this SQL statement and display the results or save them. We will look at outputting to the console and then using the Datagridview to view, sort and filter the results.

To execute these queries in Powershell we will be using the Invoke-Sqlcmd command and you might need to install it…

Install-Module sqlserver
Get-Command –ModuleName sqlserver

If it requires Admin privileges try:

install-module sqlserver -Scope  CurrentUser

For smaller environments you may not need a separate file to list all of your SQL servers and can use a simple array. The Array is designate by the ampersand or @(‘item1′,’item2′,’item3’).

$serverlist = @(
    "server\WSQL01",
    "AnotherServer",
    "Server2\WSQL05")

$query = "
SELECT 
	SERVERPROPERTY('MachineName') AS [ComputerName],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NetBios],
	SERVERPROPERTY('ServerName') AS [ServerName],
	SERVERPROPERTY('InstanceName') AS [Instance],
	SERVERPROPERTY('Edition') AS [Edition],
	SERVERPROPERTY('BuildClrVersion') AS [Build],
	-- SERVERPROPERTY('ProductBuild'),  SQL 2014 +
	SERVERPROPERTY('ProductLevel') AS [Level],
	SERVERPROPERTY('ProductUpdateLevel') AS [Update],
	SERVERPROPERTY('ProductVersion') AS [Version],
	SERVERPROPERTY('ResourceLastUpdateDateTime') AS [LastUpdate],
	SERVERPROPERTY('IsClustered') AS [Clustered], 
	SERVERPROPERTY('IsHadrEnabled') AS [AlwaysOn],
	SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IntegratedOnly],
-- 	SERVERPROPERTY('InstanceDefaultBackupPath'),  SQL 2019 +
	SERVERPROPERTY('IsSingleUser') AS [SingleUser],
	SERVERPROPERTY('InstanceDefaultDataPath') AS [DefaultDATA], 
	SERVERPROPERTY('InstanceDefaultLogPath') AS [DefaultLOG], 
	SERVERPROPERTY('Collation') AS [DefaultCollation] "

foreach ($server in $serverlist) {
    Invoke-Sqlcmd -Query $query -ServerInstance $server | Format-table

}

Large SQL Server List

For larger lists we can use a simple .TXT file to import the SQL server names into the same script. The servers.txt file has no formatting and there are no quotes around each [server\sqlinstance] name. List them one per line like this…

servername\sqlinstance
server2
server3\instance
server7\sqlinstance

## Import server list so the foreach can use it
$serverlist = Get-content "D:\temp\servers.txt"

$query = "
SELECT 
	SERVERPROPERTY('MachineName') AS [ComputerName],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NetBios],
	SERVERPROPERTY('ServerName') AS [ServerName],
	SERVERPROPERTY('InstanceName') AS [Instance],
	SERVERPROPERTY('Edition') AS [Edition],
	SERVERPROPERTY('BuildClrVersion') AS [Build],
	-- SERVERPROPERTY('ProductBuild'),  SQL 2014 +
	SERVERPROPERTY('ProductLevel') AS [Level],
	SERVERPROPERTY('ProductUpdateLevel') AS [Update],
	SERVERPROPERTY('ProductVersion') AS [Version],
	SERVERPROPERTY('ResourceLastUpdateDateTime') AS [LastUpdate],
	SERVERPROPERTY('IsClustered') AS [Clustered], 
	SERVERPROPERTY('IsHadrEnabled') AS [AlwaysOn],
	SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IntegratedOnly],
-- 	SERVERPROPERTY('InstanceDefaultBackupPath'),  SQL 2019 +
	SERVERPROPERTY('IsSingleUser') AS [SingleUser],
	SERVERPROPERTY('InstanceDefaultDataPath') AS [DefaultDATA], 
	SERVERPROPERTY('InstanceDefaultLogPath') AS [DefaultLOG], 
	SERVERPROPERTY('Collation') AS [DefaultCollation] "


foreach ($server in $serverlist) {
    Invoke-Sqlcmd -Query $query -ServerInstance $server | Format-table

}

Table Based Lists

We can also use a query to pull a list of SQL instances and the for-each will handle it the same way.

## Use a table with the SQL instances listed
$listserver = "someutility\sqlinstance"
$serverquery = "USE DBName; SELECT DISTINCT sqlinstance FROM table;"
$serverlist = Invoke-Sqlcmd -Query $serverquery -ServerInstance $listserver

$query = "
SELECT 
	SERVERPROPERTY('MachineName') AS [ComputerName],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NetBios],
	SERVERPROPERTY('ServerName') AS [ServerName],
	SERVERPROPERTY('InstanceName') AS [Instance],
	SERVERPROPERTY('Edition') AS [Edition],
	SERVERPROPERTY('BuildClrVersion') AS [Build],
	-- SERVERPROPERTY('ProductBuild'),  SQL 2014 +
	SERVERPROPERTY('ProductLevel') AS [Level],
	SERVERPROPERTY('ProductUpdateLevel') AS [Update],
	SERVERPROPERTY('ProductVersion') AS [Version],
	SERVERPROPERTY('ResourceLastUpdateDateTime') AS [LastUpdate],
	SERVERPROPERTY('IsClustered') AS [Clustered], 
	SERVERPROPERTY('IsHadrEnabled') AS [AlwaysOn],
	SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IntegratedOnly],
-- 	SERVERPROPERTY('InstanceDefaultBackupPath'),  SQL 2019 +
	SERVERPROPERTY('IsSingleUser') AS [SingleUser],
	SERVERPROPERTY('InstanceDefaultDataPath') AS [DefaultDATA], 
	SERVERPROPERTY('InstanceDefaultLogPath') AS [DefaultLOG], 
	SERVERPROPERTY('Collation') AS [DefaultCollation] "


foreach ($server in $serverlist) {
    Invoke-Sqlcmd -Query $query -ServerInstance $server | Format-table
}
## Import server list so the foreach can use it
$serverlist = Get-content "D:\temp\servers.txt"

$query = "
SELECT 
	SERVERPROPERTY('MachineName') AS [ComputerName],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NetBios],
	SERVERPROPERTY('ServerName') AS [ServerName],
	SERVERPROPERTY('InstanceName') AS [Instance],
	SERVERPROPERTY('Edition') AS [Edition],
	SERVERPROPERTY('BuildClrVersion') AS [Build],
	-- SERVERPROPERTY('ProductBuild'),  SQL 2014 +
	SERVERPROPERTY('ProductLevel') AS [Level],
	SERVERPROPERTY('ProductUpdateLevel') AS [Update],
	SERVERPROPERTY('ProductVersion') AS [Version],
	SERVERPROPERTY('ResourceLastUpdateDateTime') AS [LastUpdate],
	SERVERPROPERTY('IsClustered') AS [Clustered], 
	SERVERPROPERTY('IsHadrEnabled') AS [AlwaysOn],
	SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IntegratedOnly],
-- 	SERVERPROPERTY('InstanceDefaultBackupPath'),  SQL 2019 +
	SERVERPROPERTY('IsSingleUser') AS [SingleUser],
	SERVERPROPERTY('InstanceDefaultDataPath') AS [DefaultDATA], 
	SERVERPROPERTY('InstanceDefaultLogPath') AS [DefaultLOG], 
	SERVERPROPERTY('Collation') AS [DefaultCollation] "


foreach ($server in $serverlist) {
    $results = Invoke-Sqlcmd -Query $query -ServerInstance $server
}
$results | Out-GridView