Edit

Install the SQL Server PowerShell module

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

This article provides directions for installing the SqlServer PowerShell module.

PowerShell modules for SQL Server

There are two SQL Server PowerShell modules:

  • SqlServer: The SqlServer module includes new cmdlets to support the latest SQL features. The module also contains updated versions of the cmdlets in SQLPS. To download the SqlServer module, go to SqlServer module in the PowerShell Gallery.

  • SQLPS: The SQLPS is the module used by SQL Agent to run agent jobs in agent job steps using the PowerShell subsystem.

Note

The versions of the SqlServer module in the PowerShell Gallery support versioning and require PowerShell version 5.1 or greater.

For help topics, go to:

SQL Server Management Studio

SQL Server Management Studio (SSMS), doesn't install either PowerShell module. To use PowerShell with SSMS, install the SqlServer module from the PowerShell Gallery.

Note

SQL Server Management Studio (SSMS) 16.x (and earlier versions) included the SQLPS module.

Azure Data Studio

Azure Data Studio doesn't install either PowerShell module. To use PowerShell with Azure Data Studio, install the SqlServer module from the PowerShell Gallery.

You can use the PowerShell extension, which provides rich PowerShell editor support in Azure Data Studio.

Install or update the SqlServer module

To install the SqlServer module from the PowerShell Gallery, start a PowerShell session and run Install-Module SQLServer.

Install-Module -Name SqlServer

If running on Windows PowerShell you can use Install-Module SQLServer -Scope CurrentUser to install the module for just the current user and avoid needing elevated permissions.

Install the SqlServer module for all users

To install the SqlServer module for all users run the command below in an elevated PowerShell session; start a PowerShell session as administrator:

Install-Module -Name SqlServer

Install the SqlServer module to an offline computer

This section walks you through the steps of installing the SqlServer PowerShell module on a computer that has no access to the internet. You need two computers to accomplish this installation: a computer connected to the internet and the destination computer that is offline (not Internet connected).

  1. On a computer connected to the internet download the SQLServer PowerShell module. This command will download and save all the files for the module in the sample $env:TEMP\SQLServer\<SomeVersion> folder, which is defined in the -Path parameter. You can choose a folder of your preference as a destination on your internet-connected computer.

    Save-Module -Name SqlServer -Path $env:TEMP

  2. Browse to the folder location in File Explorer to verify that a $env:TEMP\SQLServer\<SomeVersion> folder exists or run this command:

    Get-ChildItem -Path $env:TEMP\SQLServer\

  3. Copy the $env:TEMP\SQLServer\<SomeVersion> folder you found in step 2 to the destination offline computer in %ProgramFiles%\WindowsPowerShell\Modules\SqlServer folder (this folder would work for both PS5 and PS7). Be sure to replace <SomeVersion> with the value you found in the previous step.

  4. Confirm that the SqlServer PowerShell module is available on the offline computer by running the following PowerShell cmdlet:

    Get-Module SqlServer -ListAvailable

    The output may look like this (actual version may be different and must match <SomeVersion> listed previously):

       PS C:\Users\user1> Get-Module SqlServer -ListAvailable

       Directory: C:\Program Files\WindowsPowerShell\Modules

   ModuleType Version    Name                     ExportedCommands
   ---------- -------    ----                     ----------------
   Script     22.1.1     SqlServer                {Add-RoleMember, Add-SqlAvail...

View the versions of the SqlServer module installed

Execute the following command to see the versions of the SqlServer module that have been installed

Get-Module SqlServer -ListAvailable

To view the version of the SqlServer module loaded in the current session

(Get-Module SqlServer).Version

Overwrite a previous version of the SqlServer module

You can also use the Install-Module command to overwrite a previous version.

Install-Module -Name SqlServer -AllowClobber

Note

PowerShell always uses the latest module installed.

Update the installed version of the SqlServer module

When updated versions of the SqlServer module are available, you can install the newer version using the following command:

Update-Module -Name SqlServer -AllowClobber

You can use the Update-Module command to install the newest version of the SQLServer PowerShell module, but that doesn't remove older versions. It installs the newer version side by side to allow you the ability to experiment with the latest version, yet still have older modules installed.

However, if you don't want to keep older module versions, then you can use the Uninstall-Module command to remove previous versions.

You can use the following command to list if more than one version is installed:

Get-Module SqlServer -ListAvailable

You can use the following command to remove older versions:

Uninstall-module -Name SQLServer -RequiredVersion "<version number>"

Troubleshoot

If you run into problems installing, see the Install-Module documentation and Install-Module reference.

Use a specific version of the SqlServer module

To use a specific version of the module, import it with a specific version number similar to the following command:

Import-Module SqlServer -Version 21.1.18218

Pre-release versions of the SqlServer module

Pre-release (or "preview") versions of the SqlServer module may be available in the PowerShell Gallery.

Important

These versions may be discovered and installed by using the updated Find-Module and Install-Module cmdlets that are part of the PowerShellGet module by passing the -AllowPrerelease switch. To use these cmdlets, install the PowerShellGet module and then open a new session.

Discover pre-release versions of the SqlServer module

To discover the pre-release (preview) versions of the SqlServer module, run the following command:

Find-Module SqlServer -AllowPrerelease

Install a specific pre-release version of the SqlServer module

To install a specific pre-release version of the module, install it with a specific version number.

You can try to use the following command:

Install-Module SqlServer -RequiredVersion 21.1.18218-preview -AllowPrerelease

SQL Server PowerShell on Linux

Visit Manage SQL Server on Linux with PowerShell to see how to install SQL Server PowerShell on Linux.

Other modules

  • Az.Sql - SQL service cmdlets for Azure Resource Manager in Windows PowerShell and PowerShell.

  • SqlServerDsc - Module with DSC resources for deployment and configuration of Microsoft SQL Server.

Cmdlet reference

Next steps