PowerShell SQL Server Tutorial

PowerShell provides the SQLServer module in order to work with the SQL Database server or Microsoft SQL Database Server. There is also the SQLPS module that is used to manage SQL server configuration.

SQLServer Module

The SQLServer module is mainly used to connect and consume the SQL server via the PowerShell command line interface. The SQLServer module is not installed by default. But we can install the SQLServer module easily with the following Install-Module command. The following command installs the SQLServer module for the system and for all users.

PS> Install-Module SQLServer

We can also install the SQLServer for the current user. The -Scope CurrentUser is used to limit SQLServer module installation for the current user.

PS> Install-Module -Scope CurrentUser SQLServer

List the SQLServer module information like version.

PS> Get-Module SQLServer -ListAvailable

We can see that the install SQLServer module version is 21.1.18256 and provided or exported commands are listed like Add-RoleMember, Add-SqlAvailability…

The SQLServer module is updated regularly and if the current installation is out of date we can update the SQLServer module with the Update-Module command like below.

PS> Update-Module SQLServer 

In order to use the SQLServer module we should import it into the current PowerShell environment or script. The Import-Module command is used to import SQLServer like below.

PS> Import-Module SQLServer 

Connect SQL Server

In the following PowerShell script we create the $ConnectionString to provide database related parameters. The connection object is created into the $Connection object. We can connect to the Database server using the Open() method. We can check the connection status with the State attribute.

$ConnectionString = "Data Source=192.168.1.0;Database=customers;User ID=admin;Password=T0Ps3Cr1T"

$Connection= New-Object System.Data.SqlClient.SqlConnection $ConnectionString

$Connection.Open()
if($Connection.State -eq "Open")
{
     Write-Host "Test connection successful"
     $Connection.Close()
 }

SQLPS Module

The SQLPS is another powershell module which provides commands to manage SQL server. The SQLPS provides generally configuration methods about SQL server. We can install the SQLPS module via the PowerShell like below.

PS> Install-Module SQLPS

After installing the SQLPS module the following commands can be used to manage SQL server and related features.

Add-SqlAvailabilityDatabase	Adds primary databases to an availability group or joins secondary databases to an availability group.
Add-SqlAvailabilityGroupListenerStaticIp	Adds a static IP address to an availability group listener.
Backup-SqlDatabase	Backs up SQL Server database objects.
Convert-UrnToPath	Converts a SQL Server Management Object URN to a Windows PowerShell provider path.
Decode-SqlName	Decodes an encoded SQL Server identifier name.
Disable-SqlAlwaysOn	Disables the AlwaysOn availability groups feature for a server.
Enable-SqlAlwaysOn	Enables the AlwaysOn availability groups feature.
Encode-SqlName	Encodes special characters in SQL Server names to characters that can be used in PowerShell paths.
Invoke-PolicyEvaluation	Invokes one or more SQL Server policy-based management policy evaluations.
Invoke-Sqlcmd	Runs a script containing statements supported by the SQL Server SQLCMD utility.
Join-SqlAvailabilityGroup	Joins the local secondary replica to an availability group.
New-SqlAvailabilityGroup	Creates an availability group.
New-SqlAvailabilityGroupListener	Creates an availability group listener and attaches it to an availability group.
New-SqlAvailabilityReplica	Creates an availability replica.
New-SqlHADREndpoint	Creates a database mirroring endpoint on a SQL Server instance.
Remove-SqlAvailabilityDatabase	Removes an availability database from its availability group.
Remove-SqlAvailabilityGroup	Removes an availability group.
Restore-SqlDatabase	Restores a database from a backup or transaction log records.
Resume-SqlAvailabilityDatabase	Resumes data movement on an availability database.
Set-SqlAvailabilityGroup	Sets settings on an availability group.
Set-SqlAvailabilityGroupListener	Sets the port setting on an availability group listener.
Set-SqlAvailabilityReplica	Sets the settings on an availability replica.
Set-SqlHADREndpoint	Sets the properties of a database mirroring endpoint.
Suspend-SqlAvailabilityDatabase	Suspends data movement on an availability database.
Switch-SqlAvailabilityGroup	Starts a failover of an availability group to a secondary replica.
Test-SqlAvailabilityGroup	Evaluates the health of an availability group.
Test-SqlAvailabilityReplica	Evaluates the health of availability replicas.
Test-SqlDatabaseReplicaState	Evaluates the health of an availability database.