How to Check SQL Server Version Using PowerShell

1. Use the sqlcmd Utility to Check the SQL Server Version in PowerShell
2. Advantages and Disadvantages of Using the sqlcmd Utility
3. Use Invoke-Sqlcmd Cmdlet to Check the SQL Server Version Using PowerShell
4. Advantages and Disadvantages of Using the Invoke-Sqlcmd Cmdlet to Check the SQL Server Version in PowerShell
5. Difference Between sqlcmd Utility and Invoke-Sqlcmd Cmdlet
6. Conclusion

Checking the version of a program is one of the common operations you can do in PowerShell. If you’re using the Microsoft SQL server for managing relational databases, it is important to know which version is installed on your computer.

This tutorial will introduce two methods to check the SQL server version with PowerShell.

Use the sqlcmd Utility to Check the SQL Server Version in PowerShell

The sqlcmd is a command-line utility that allows you to run interactive Transact-SQL statements and scripts. It helps to automate Transact-SQL scripting tasks.

Syntax:

sqlcmd -S ServerName\InstanceName -Q "SQL Query"

In the syntax above, we replace ServerName with the name of your SQL Server, InstanceName with the name of your SQL Server instance, and "SQL Query" with the specific SQL query you want to execute.

The following command prints the SQL server version in PowerShell. The DelftStack is the server name, and the SQLEXPRESS is the instance name of our SQL server.

sqlcmd -S DelftStack\SQLEXPRESS -Q "SELECT @@VERSION"

We are using the sqlcmd utility, which is a command-line tool for executing Transact-SQL commands and scripts. It’s a way to interact with SQL Server from the command line.

Using -S DelftStack\SQLEXPRESS, we are specifying the server and instance we want to connect to. DelftStack is the server name, and SQLEXPRESS is the instance name, and this combination identifies the specific SQL Server we want to query.

The -Q "SELECT @@VERSION" part of the command instructs SQL Server to execute a SQL query. In this case, we are requesting the SQL Server to retrieve its version information.

The SELECT @@VERSION query is a built-in SQL Server command that returns detailed version information.

Output:

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
    Sep 24 2019 13:48:23
    Copyright (C) 2019 Microsoft Corporation
    Express Edition (64-bit) on Windows 10 Home Single Language 10.0 <X64> (Build 22000: )

(1 rows affected)

The output displays detailed information about the SQL Server version, including the release and build information. It shows that the installed version of the SQL server is 15.0.2000.5.

Important to note that the sqlcmd utility must be already installed and accessible in the system’s PATH. If it is not installed or not accessible, the command will fail.

To install sqlcmd in PowerShell, you need to install the SQL Server Command Line Utilities, which include sqlcmd.

Here are the steps to install sqlcmd:

Keep in mind that depending on the version of SQL Server, the particular steps and the name of the installer may differ and the installation package you choose. Be sure to select the appropriate components during installation to ensure that sqlcmd is included.

Additionally, the server name and instance name provided in the command should be valid and accessible. If they are incorrect or inaccessible, the command will fail.

Advantages and Disadvantages of Using the sqlcmd Utility

Advantages:

• Command-line control for scripting and automation.
• No need to install an additional module.
• Widespread support across SQL Server versions.

Disadvantages:

• Output can be less user-friendly, requiring extra parsing.
• Limited features compared to Invoke-Sqlcmd.

Use Invoke-Sqlcmd Cmdlet to Check the SQL Server Version Using PowerShell

The Invoke-Sqlcmd cmdlet executes the scripts and commands supported by the SQL Server SQLCMD utility. It accepts Transact-SQL statements and commands such as GO and QUIT.

Syntax:

Invoke-Sqlcmd -query "SQL Query" -ServerInstance "ServerName\InstanceName"

In the syntax above, we replace "SQL Query" with the specific SQL query you want to execute and "ServerName\InstanceName" with the server and instance name of the SQL Server you want to connect to. This command is used to run SQL queries from within PowerShell and is particularly useful for integrating SQL Server tasks into PowerShell scripts.

The following command gets the SQL server version in PowerShell.

Invoke-Sqlcmd -query "SELECT @@VERSION" -ServerInstance "DELFT-PC\SQLEXPRESS"

We are using the Invoke-Sqlcmd cmdlet, which is a part of PowerShell and is specifically designed for interacting with SQL Server. It allows us to execute SQL commands and queries directly from within a PowerShell script or session.

The -query "SELECT @@VERSION" part of the command specifies the SQL query we want to run. In this case, we are executing the SQL query SELECT @@VERSION, and this query is a built-in SQL Server command that retrieves detailed version information about the SQL Server it’s connected to.

Using -ServerInstance "DELFT-PC\SQLEXPRESS", we are defining the server and instance to connect to with the following components:

1. -ServerInstance is an option that indicates the server and instance we want to connect to.

2. "DELFT-PC\SQLEXPRESS" is the server name and instance name combined. "DELFT-PC" represents the name of the SQL Server, and "SQLEXPRESS" represents the specific instance of SQL Server installed on that machine, and this combination specifies the exact SQL Server we want to query.

Output:

Column1
-------
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) ...

The output above provides us with detailed information about the SQL Server version, including release and build details.

If the Invoke-Sqlcmd cmdlet is not recognized, you must install it using the below command:

Install-Module SqlServer

Then, run this command to import the module.

Import-Module SqlServer

This approach is convenient for integrating SQL Server tasks into PowerShell scripts and leveraging the power of PowerShell for data manipulation and automation.

Important to note that the Invoke-Sqlcmd cmdlet is already available in the current PowerShell session. If the required module is not imported, the code will fail with an error.

Additionally, if the specified SQL Server instance ("DELFT-PC\SQLEXPRESS") is incorrect or inaccessible, the code will fail to connect and execute the query.

Advantages and Disadvantages of Using the Invoke-Sqlcmd Cmdlet to Check the SQL Server Version in PowerShell

Advantages:

• Seamless integration with PowerShell for advanced scripting.
• More structured and user-friendly output.
• A rich feature set for complex database tasks.

Disadvantages:

• Requires the installation of the SqlServer module.
• Compatibility with SQL Server versions may vary.

Difference Between sqlcmd Utility and Invoke-Sqlcmd Cmdlet

The primary difference between the two methods, using the sqlcmd utility and the Invoke-Sqlcmd cmdlet, to check the SQL Server version in PowerShell can be summarized in terms of their approach, integration, and usability.

The key distinction lies in the fact that sqlcmd is an external utility with limited integration into PowerShell. It operates as a separate command-line tool, and while it can be used within PowerShell scripts, it does not provide the same level of integration and flexibility as Invoke-Sqlcmd.

On the other hand, Invoke-Sqlcmd is a PowerShell-native cmdlet designed specifically for interacting with SQL Server. It offers a more powerful and integrated approach for managing SQL Server data within PowerShell scripts, and this cmdlet allows for seamless integration with PowerShell, structured output, and advanced data manipulation capabilities.

Conclusion

This tutorial explores two methods for checking the SQL Server version in PowerShell: using the sqlcmd utility and the Invoke-Sqlcmd cmdlet.

The sqlcmd utility, a command-line tool, offers command-line control and does not require additional module installations. However, its output may require additional parsing, and it has limited features compared to Invoke-Sqlcmd.

The Invoke-Sqlcmd cmdlet, a native PowerShell tool, provides seamless integration with PowerShell, structured and user-friendly output, and a broad range of SQL Server features. It does require the installation of the SqlServer module.

As a best practice, we should be aware that software and module names may change over time, and it’s essential to verify the module name and version based on their current environment. This ensures that the provided code and methods remain relevant and functional.