SQL Server PowerShell Modules

PowerShell provides a powerful command-line shell and scripting language for managing SQL Server. Several modules are available to interact with SQL Server components, enabling automation of various administrative tasks.

Key PowerShell Modules for SQL Server

  • SqlServer (SQLServer)
    This is the primary module for managing SQL Server instances, databases, logins, jobs, and other core objects. It provides cmdlets for common tasks like creating databases, backing up and restoring, and managing server configurations. 
    # Get information about all SQL Server instances on the local machine
Get-SqlServerInstance

# Connect to a specific SQL Server instance
$server = "YourServerName"
Connect-SqlServer -ServerInstance $server

# Get all databases on the connected instance
Get-SqlDatabase

# Create a new database
New-SqlDatabase -Name "MyNewDatabase" -Owner "sa" -Path "SqlServer:\SQL\YourServerName\MSSQLSERVER"
  • DataSqlPackage (SqlPackage)
    This module works with the SqlPackage.exe command-line utility, which is used to publish or deploy SQL Server database projects, export database schema and data, and import them. It's invaluable for CI/CD pipelines. 
    # Export a database schema to a .dacpac file
SqlPackage /Action:Export /SourceDatabaseName:"MyDatabase" /TargetFile:"C:\Dacpac\MyDatabase.dacpac"

# Deploy a .dacpac file to a SQL Server instance
SqlPackage /Action:Publish /SourceFile:"C:\Dacpac\MyDatabase.dacpac" /TargetServerName:"YourServerName" /TargetDatabaseName:"MyNewDatabase"
  • DbaTools (dbatools)
    A community-driven, open-source PowerShell module with over 300 cmdlets for SQL Server administration, including migration, patching, performance tuning, and troubleshooting. Highly recommended for its extensive capabilities. 
    # Install dbatools if you haven't already
# Install-Module -Name dbatools

# Migrate a database from one server to another
Copy-DbaDatabase -SourceServer "OldServer" -DestinationServer "NewServer" -DatabaseName "MyDatabase"

# Check SQL Server Agent jobs across multiple servers
Get-DbaAgentJob -ComputerName "Server1", "Server2"
  • SqlServerManagement (SMO)
    While the SqlServer module is preferred for most tasks, the SQL Server Management Objects (SMO) library can be accessed programmatically in PowerShell for advanced scenarios and fine-grained control over SQL Server objects. 
    # Load the SMO assembly
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=16.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=16.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

# Connect to the server using SMO
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("YourServerName")

# Get a database object
$database = $server.Databases["MyDatabase"]

# List tables in the database
foreach ($table in $database.Tables) {
    Write-Host $table.Name
}

Getting Started

To use these modules, you typically need to install them from the PowerShell Gallery. Open a PowerShell console as an administrator and run: 

# Install the official SqlServer module
Install-Module -Name SqlServer -Force

# Install the popular dbatools module
Install-Module -Name dbatools -Force

You can then import the modules into your session using Import-Module or let PowerShell auto-load them when you call their cmdlets.