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 SqlServer
Display 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 SqlServer
ModuleType 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 $crendential
Run 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