PowerShell provides the SQLServer module in order to connect and use the SQL database servers. We can use the SQLServer module in order to connect and run queries in the connected database server. In this tutorial, we examine how to import and use the SQLServer module to connect SQL Server Database.
Import SQLServer Module
We should use the Import-Module command in order to use the functions.
PS> Import-Module SqlServerDisplay SqlServer Module Information
We can use the Get-Module in order to display information about the SqlServer module. This command provides information like ModuleType, Version, NAme, and ExportedCommands. The ExportedCommands is very important as it lists all provided functions to connect and query the SQL database server.
PS> Get-Module -Name SqlServerModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 21.1.1123 SqlServer {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupList...
Connect SQL Server
After importing the SQLServer module we can connect to the SQL server by using the Get-SqlInstance command providing the remote SQL server hostname or IP address and required credentials to authenticate the SQL server. The -Get-Credential command is used to request credentials interactive from the command line interface.
PS> $crendentials = Get-Credential
PS> $instance = Get-SqlInstance -ServerInstance 192.168.1.10 -Credential $crendentialRun Query In SQL Server
The Invoke-Sqlcmd command is used to run SQL or query the database. We should provide the $instance to the Invoke-Sqlcmd command using the -ServerInstance parameter. The query is specified with the -Query parameter.
PS> $crendentials = Get-Credential
PS> $instance = Get-SqlInstance -ServerInstance 192.168.1.10 -Credential $crendential
PS> Invoke-Sqlcmd -Query "SELECT * FROM Users" -ServerInstance $instance