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=;Database=customers;User ID=admin;Password=T0Ps3Cr1T"

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

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

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-SqlAvailabilityDatabaseAdds primary databases to an availability group or joins secondary databases to an availability group.
Add-SqlAvailabilityGroupListenerStaticIpAdds a static IP address to an availability group listener.
Backup-SqlDatabaseBacks up SQL Server database objects.
Convert-UrnToPathConverts a SQL Server Management Object URN to a Windows PowerShell provider path.
Decode-SqlNameDecodes an encoded SQL Server identifier name.
Disable-SqlAlwaysOnDisables the AlwaysOn availability groups feature for a server.
Enable-SqlAlwaysOnEnables the AlwaysOn availability groups feature.
Encode-SqlNameEncodes special characters in SQL Server names to characters that can be used in PowerShell paths.
Invoke-PolicyEvaluationInvokes one or more SQL Server policy-based management policy evaluations.
Invoke-SqlcmdRuns a script containing statements supported by the SQL Server SQLCMD utility.
Join-SqlAvailabilityGroupJoins the local secondary replica to an availability group.
New-SqlAvailabilityGroupCreates an availability group.
New-SqlAvailabilityGroupListenerCreates an availability group listener and attaches it to an availability group.
New-SqlAvailabilityReplicaCreates an availability replica.
New-SqlHADREndpointCreates a database mirroring endpoint on a SQL Server instance.
Remove-SqlAvailabilityDatabaseRemoves an availability database from its availability group.
Remove-SqlAvailabilityGroupRemoves an availability group.
Restore-SqlDatabaseRestores a database from a backup or transaction log records.
Resume-SqlAvailabilityDatabaseResumes data movement on an availability database.
Set-SqlAvailabilityGroupSets settings on an availability group.
Set-SqlAvailabilityGroupListenerSets the port setting on an availability group listener.
Set-SqlAvailabilityReplicaSets the settings on an availability replica.
Set-SqlHADREndpointSets the properties of a database mirroring endpoint.
Suspend-SqlAvailabilityDatabaseSuspends data movement on an availability database.
Switch-SqlAvailabilityGroupStarts a failover of an availability group to a secondary replica.
Test-SqlAvailabilityGroupEvaluates the health of an availability group.
Test-SqlAvailabilityReplicaEvaluates the health of availability replicas.
Test-SqlDatabaseReplicaStateEvaluates the health of an availability database.

Leave a Comment