Skip to content

SQL Server PowerShell

    Install SQL Server PowerShell

    There are two SQL Server PowerShell modules; SqlServer and SQLPS.

    The SqlServer module is the current PowerShell module to use.

    The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

    The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

    Previous versions of the SqlServer module were included with SQL Server Management Studio (SSMS), but only with the 16.x versions of SSMS.

    To use PowerShell with SSMS 17.0 and later, install the SqlServer module from the PowerShell Gallery.

    You can also use PowerShell with Azure Data Studio.

    Why did the module change from SQLPS to SqlServer?

    To ship SQL PowerShell updates, we had to change the identity of the SQL PowerShell module, and the wrapper known as SQLPS.exe. Because of this change, there are now two SQL PowerShell modules, the SqlServer module, and the SQLPS module.

    Update your PowerShell scripts if you import the SQLPS module.

    If you have any PowerShell scripts that run Import-Module -Name SQLPS, and you want to take advantage of the new provider functionality and new cmdlets, you must change them to Import-Module -Name SqlServer. The new module is installed to %ProgramFiles%\WindowsPowerShell\Modules\SqlServer folder. As such, you don’t have to update the $env:PSModulePath variable. If you have scripts that use a third-party or community version of a module named SqlServer, use the Prefix parameter to avoid name collisions.

    It is recommended to start your script with Import-Module SQLServer to avoid side-by-side issues if the SQLPS module is installed on the same machine.

    This section applies to scripts executed from PowerShell and not the SQL Agent. The new module can be used with SQL Agent job steps using #NOSQLPS.

    SQL Server PowerShell Components

    The SqlServer module comes with:

    • PowerShell Providers, which enables a simple navigation mechanism similar to file system paths. You can build paths similar to file system paths, where the drive is associated with a SQL Server management object model, and the nodes are based on the object model classes. You can then use familiar commands such as cd and dir to navigate the paths similar to the way you navigate folders in a command prompt window. You can use other commands, such as ren or del, to perform actions on the nodes in the path.
    • A set of cmdlets that support actions such as running a sqlcmd script containing Transact-SQL or XQuery statements.
    • The AS provider and cmdlets, which before they were installed separately.

    SQL Server versions

    SQL PowerShell cmdlets can be used to manage instances of Azure SQL Database, Azure Synapse Analytics, and all supported SQL Server products.

    SQL Server identifiers that contain characters not supported in PowerShell paths

    The Encode-Sqlname and Decode-Sqlname cmdlets help you specify SQL Server identifiers that contain characters not supported in PowerShell paths.

    Use the Convert-UrnToPath cmdlet to convert a Unique Resource Name for a Database Engine object to a path for the SQL Server PowerShell provider.

    Query Expressions and Unique Resource Names

    Query expressions are strings that use syntax similar to XPath to specify a set of criteria that enumerates one or more objects in an object model hierarchy. A Unique Resource Name (URN) is a specific type of query expression string that uniquely identifies a single object.

    SQL Server Agent

    There’s no change to the module used by SQL Server Agent. As such, SQL Server Agent jobs, which have PowerShell type job steps use the SQLPS module. However, starting with SQL Server 2019, you can disable SQLPS. To do so, on the first line of a job step of the type PowerShell you can add #NOSQLPS, which stops the SQL Agent from auto-loading the SQLPS module. When you do this, your SQL Agent Job runs the version of PowerShell installed on the machine, and then you can use any other PowerShell module you like.

    If you want to use the SqlServer module in your SQL Agent Job step, you can place this code on the first two lines of your script.

    PowerShell

    #NOSQLPS

    Import-Module -Name SqlServer

    Manage SQL Server on Linux with PowerShell Core

    This introduces SQL Server PowerShell and walks you through a couple of examples on how to use it with PowerShell Core (PS Core) on macOS & Linux. PowerShell Core is now an Open Source project on GitHub.

    Cross-platform editor options

    All of the steps PowerShell Core below will work in a regular terminal, or you can run them from a terminal within VS Code or Azure Data Studio. Both VS Code and Azure Data Studio are available on macOS and Linux. For more information on Azure Data Studio, see this quickstart. You may also want to consider using the PowerShell extension for it.

    Install the SqlServer module

    The SqlServer module is maintained in the PowerShell Gallery. When working with SQL Server, you should always use the most recent version of the SqlServer PowerShell module.

    To install the SqlServer module, open a PowerShell Core session and run the following code:

    powerhsell

    Install-Module -Name SqlServer

    Using the SqlServer module

    Let’s start by launching PowerShell Core. If you are on macOS or Linux, Open a terminal session on your computer, and type pwsh to launch a new PowerShell Core session. On Windows, use Win+R, and type pwsh to launch a new PowerShell Core session.

    pwsh

    SQL Server provides a PowerShell module named SqlServer. You can use the SqlServer module to import the SQL Server components (SQL Server provider and cmdlets) into a PowerShell environment or script.

    Copy and paste the following command at the PowerShell prompt to import the SqlServer module into your current PowerShell session:

    PowerShell

    Import-Module SqlServer

    Type the following command at the PowerShell prompt to verify that the SqlServer module was imported correctly:

    PowerShell

    Get-Module -Name SqlServer

    PowerShell should display information similar to the following output:

    ModuleType Version    Name          ExportedCommands

    ———- ——-    —-          —————-

    Script     21.1.18102 SqlServer     {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupList…

    Connect to SQL Server and get server information

    The following steps use PowerShell Core to connect to your SQL Server instance on Linux and display a couple of server properties.

    Copy and paste the following commands at the PowerShell prompt. When you run these commands, PowerShell will:

    • Display a dialog that prompts you for the hostname or IP address of your instance
    • Display the PowerShell credential request dialog, which prompts you for the credentials. You can use your SQL username and SQL password to connect to your SQL Server instance on Linux
    • Use the Get-SqlInstance cmdlet to connect to the Server and display a few properties

    Optionally, you can just replace the $serverInstance variable with the IP address or the hostname of your SQL Server instance.

    PowerShell

    # Prompt for instance & credentials to login into SQL Server

    $serverInstance = Read-Host “Enter the name of your instance”

    $credential = Get-Credential

    # Connect to the Server and return a few properties

    Get-SqlInstance -ServerInstance $serverInstance -Credential $credential

    # done

    PowerShell should display information similar to the following output:

    Instance Name                   Version    ProductLevel UpdateLevel  HostPlatform HostDistribution

    ————-                   ——-    ———— ———–  ———— —————-

    your_server_instance            14.0.3048  RTM          CU13         Linux        Ubuntu

     Note

    If nothing is displayed for these values, the connection to the target SQL Server instance most likely failed. Make sure that you can use the same connection information to connect from SQL Server Management Studio.

    Using the SQL Server PowerShell Provider

    Another option for connecting to your SQL Server instance is to use the SQL Server PowerShell Provider. Using the provider allows you to navigate SQL Server instance similar to as if you were navigating the tree structure in Object Explorer, but at the cmdline. By default this provider is presented as a PSDrive named SQLSERVER:\ which you can use to connect & navigate SQL Server instances that your domain account has access to.

    You can also use SQL authentication with the SQL Server PowerShell Provider. To do this, use the New-PSDrive cmdlet to create a new PSDrive and supply the proper credentials to connect.

    In this example below, you will see an example of how to create a new PSDrive using SQL authentication.

    PowerShell

    # NOTE: We are reusing the values saved in the $credential variable from the above example.

    New-PSDrive -Name SQLonDocker -PSProvider SqlServer -Root ‘SQLSERVER:\SQL\localhost,10002\Default\’ -Credential $credential

    You can confirm that the drive was created by running the Get-PSDrive cmdlet.

    PowerShell

    Get-PSDrive

    Once you have created your new PSDrive, you can start navigating it.

    PowerShell

    dir SQLonDocker:\Databases

    Here is what the output might look like. You might notice this output is similar to what SSMS will display at the Databases node. It displays the user databases, but not the system databases.

    PowerShell

    Name                 Status           Size     Space  Recovery Compat. Owner

                                                Available  Model     Level

    —-                 ——           —- ———- ——– ——- —–

    AdventureWorks2016   Normal      209.63 MB    1.31 MB Simple       130 sa

    AdventureWorksDW2012 Normal      167.00 MB   32.47 MB Simple       110 sa

    AdventureWorksDW2014 Normal      188.00 MB   78.10 MB Simple       120 sa

    AdventureWorksDW2016 Normal      172.00 MB   74.76 MB Simple       130 sa

    AdventureWorksDW2017 Normal      208.00 MB   40.57 MB Simple       140 sa

    If you need to see all databases on your instance, one option is to use the Get-SqlDatabase cmdlet.

    Get Databases

    An important cmdlet to know is the Get-SqlDatabase. For many operations that involve a database, or objects within a database, the Get-SqlDatabase cmdlet can be used. If you supply values for both the -ServerInstance and -Database parameters, only that one database object will be retrieved. However, if you specify only the -ServerInstance parameter, a full list of all databases on that instance will be returned.

    PowerShell

    # NOTE: We are reusing the values saved in the $credential variable from the above example.

    # Connect to the Instance and retrieve all databases

    Get-SqlDatabase -ServerInstance ServerB -Credential $credential

    Here is a sample of what might be returned by the Get-SqlDatabase command above:

    PowerShell

    Name                 Status           Size     Space  Recovery Compat. Owner

                                                Available  Model     Level

    —-                 ——           —- ———- ——– ——- —–

    AdventureWorks2016   Normal      209.63 MB    1.31 MB Simple       130 sa

    AdventureWorksDW2012 Normal      167.00 MB   32.47 MB Simple       110 sa

    AdventureWorksDW2014 Normal      188.00 MB   78.10 MB Simple       120 sa

    AdventureWorksDW2016 Normal      172.00 MB   74.88 MB Simple       130 sa

    AdventureWorksDW2017 Normal      208.00 MB   40.63 MB Simple       140 sa

    master               Normal        6.00 MB  600.00 KB Simple       140 sa

    model                Normal       16.00 MB    5.70 MB Full         140 sa

    msdb                 Normal       15.50 MB    1.14 MB Simple       140 sa

    tempdb               Normal       16.00 MB    5.49 MB Simple       140 sa

    Examine SQL Server error logs

    The following steps use PowerShell Core to examine error logs connect on your SQL Server instance on Linux.

    Copy and paste the following commands at the PowerShell prompt. They might take a few minutes to run. These commands do the following steps:

    • Display a dialog that prompts you for the hostname or IP address of your instance
    • Display the PowerShell credential request dialog that prompts you for the credentials. You can use your SQL username and SQL password to connect to your SQL Server instance on Linux
    • Use the Get-SqlErrorLog cmdlet to connect to the SQL Server instance on Linux and retrieve error logs since Yesterday

    Optionally, you can replace the $serverInstance variable with the IP address or the hostname of your SQL Server instance.

    PowerShell

    # Prompt for instance & credentials to login into SQL Server

    $serverInstance = Read-Host “Enter the name of your instance”

    $credential = Get-Credential

    # Retrieve error logs since yesterday

    Get-SqlErrorLog -ServerInstance $serverInstance -Credential $credential -Since Yesterday

    # done

    Explore cmdlets currently available in PS Core

    While the SqlServer module currently has 109 cmdlets available in Windows PowerShell, only 62 of the 109 are available in PSCore. A full list of 62 cmdlets currently available is included below.

    The following command will show you all of the cmdlets available on the version of PowerShell you are using.

    PowerShell

    Get-Command -Module SqlServer -CommandType Cmdlet |

    Sort-Object -Property Noun |

    SELECT Name

    • ConvertFrom-EncodedSqlName
    • ConvertTo-EncodedSqlName
    • Get-SqlAgent
    • Get-SqlAgentJob
    • Get-SqlAgentJobHistory
    • Get-SqlAgentJobSchedule
    • Get-SqlAgentJobStep
    • Get-SqlAgentSchedule
    • Invoke-SqlAssessment
    • Get-SqlAssessmentItem
    • Remove-SqlAvailabilityDatabase
    • Resume-SqlAvailabilityDatabase
    • Add-SqlAvailabilityDatabase
    • Suspend-SqlAvailabilityDatabase
    • New-SqlAvailabilityGroup
    • Set-SqlAvailabilityGroup
    • Remove-SqlAvailabilityGroup
    • Switch-SqlAvailabilityGroup
    • Join-SqlAvailabilityGroup
    • Revoke-SqlAvailabilityGroupCreateAnyDatabase
    • Grant-SqlAvailabilityGroupCreateAnyDatabase
    • New-SqlAvailabilityGroupListener
    • Set-SqlAvailabilityGroupListener
    • Add-SqlAvailabilityGroupListenerStaticIp
    • Set-SqlAvailabilityReplica
    • Remove-SqlAvailabilityReplica
    • New-SqlAvailabilityReplica
    • Set-SqlAvailabilityReplicaRoleToSecondary
    • New-SqlBackupEncryptionOption
    • Get-SqlBackupHistory
    • Invoke-Sqlcmd
    • New-SqlCngColumnMasterKeySettings
    • Remove-SqlColumnEncryptionKey
    • Get-SqlColumnEncryptionKey
    • Remove-SqlColumnEncryptionKeyValue
    • Add-SqlColumnEncryptionKeyValue
    • Get-SqlColumnMasterKey
    • Remove-SqlColumnMasterKey
    • New-SqlColumnMasterKey
    • Get-SqlCredential
    • Set-SqlCredential
    • New-SqlCredential
    • Remove-SqlCredential
    • New-SqlCspColumnMasterKeySettings
    • Get-SqlDatabase
    • Restore-SqlDatabase
    • Backup-SqlDatabase
    • Set-SqlErrorLog
    • Get-SqlErrorLog
    • New-SqlHADREndpoint
    • Set-SqlHADREndpoint
    • Get-SqlInstance
    • Add-SqlLogin
    • Remove-SqlLogin
    • Get-SqlLogin
    • Set-SqlSmartAdmin
    • Get-SqlSmartAdmin
    • Read-SqlTableData
    • Write-SqlTableData
    • Read-SqlViewData
    • Read-SqlXEvent
    • Convert-UrnToPath