Share via

  • Article

Running SQL Server PowerShell

SQL Server installs Windows PowerShell and a set of SQL Server snap-ins that expose SQL Server functionality in Windows PowerShell. You can then code Windows PowerShell scripts that work with SQL Server objects. The scripts can be run in the Windows PowerShell environment, in SQL Server Management Studio, and as SQL Server Agent jobs.

Installing SQL Server PowerShell Support

You install the software required to run Windows PowerShell scripts by using SQL Server Setup. Beginning in SQL Server 2008, Setup installs the following Windows PowerShell components when you select either the client software or the Database Services nodes:

  • Windows PowerShell 1.0, if Windows PowerShell is not already present on your computer.

  • The SQL Server snap-ins. The snap-ins are dll files that implement two types of Windows PowerShell support for SQL Server:

    • A set of SQL Server cmdlets. Cmdlets are commands that implement a specific action. For example, Invoke-Sqlcmd runs a Transact-SQL or XQuery script that can also be run by using the sqlcmd utility, and Invoke-PolicyEvaluation reports whether SQL Server objects comply with policy-based management policies.

    • A SQL Server provider. The provider lets you navigate the hierarchy of SQL Server objects using a path similar to a file system path. Each object is associated with a class from the SQL Server Management object models. You can use the methods and properties of the class to perform work on the objects. For example, if you cd to a databases object in a path, you can use the methods and properties of the Microsoft.SqlServer.Managment.SMO.Database class to manage the database.

  • The sqlps utility that is used to run Windows PowerShell sessions that include the SQL Server snap-ins.

Beginning in SQL Server 2008, SQL Server Management Studio supports starting Windows PowerShell sessions from the Object Explorer tree. Also beginning in SQL Server 2008, SQL Server Agent supports Windows PowerShell job steps.

If Windows PowerShell is uninstalled after Setup finishes, the SQL Server features for Windows PowerShell will not function. Windows PowerShell can be uninstalled by Windows users, and uninstalling Windows PowerShell might be required by some Windows operating system upgrades. If Windows PowerShell has been uninstalled and you want to use the SQL Server features, you must do one of the following:

  • Manually download and reinstall Windows PowerShell 1.0 from the Microsoft Download Center. Download instructions are on the Windows Server 2003 Web site.

  • If you are running Windows Server 2008, Windows PowerShell 1.0 exists in the operating system but is disabled by default. You can re-enable Windows PowerShell from Windows Server 2008.

Supported Versions of SQL Server

You must use the SQL Server 2008 client components to run Windows PowerShell. Windows PowerShell can connect to instances of SQL Server 2000 or later. The earliest version of SQL Server 2005 that you can use is SP2. The earliest version of SQL Server 2000 that you can use is SP4. When Windows PowerShell connects to SQL Server 2005 and SQL Server 2000, it is limited to the functionality available in those versions of SQL Server.

Using the sqlps Utility

sqlps is a utility that creates a Windows PowerShell environment and then loads and registers the SQL Server snap-ins. You can use sqlps to:

  • Interactively run Windows PowerShell commands.

  • Run Windows PowerShell script files.

  • Run SQL Server cmdlets.

  • Use the SQL Server provider paths to navigate through the hierarchy of SQL Server objects.

By default, sqlps runs with the scripting execution policy set to Restricted, which prevents running any Windows PowerShell scripts. You can use the Set-ExecutionPolicy cmdlet to enable running signed scripts, or any scripts. Only run scripts from trusted sources, and secure all input and output files using the appropriate NTFS permissions. For more information about enabling Windows PowerShell scripts, see Running Windows PowerShell Scripts.

For more information, see sqlps Utility.

Using Windows PowerShell in SQL Server Management Studio

You can start Windows PowerShell sessions in SQL Server Management Studio by right-clicking objects in Object Explorer and selecting Start PowerShell. SQL Server Management Studio then starts a Windows PowerShell session in which the SQL Server PowerShell snap-ins have been loaded and registered. The path for the session is preset to the location of the object you right clicked in Object Explorer. For example, if you right-click the AdventureWorks2008R2 database object in Object Explorer and select Start PowerShell, the Windows PowerShell path is set to the following:

SQLSERVER:\SQL\MyComputer\MyInstance\Databases\AdventureWorks2008R2>

Using Windows PowerShell in SQL Server Agent Job Steps

There are several types of SQL Server Agent job steps. Each type is associated with a subsystem that implements a specific environment, such as a replication agent or command prompt environment. The SQL Server Agent subsystem for Windows PowerShell supports job steps that execute Windows PowerShell scripts. You can code Windows PowerShell scripts, and then use SQL Server Agent to include the scripts in jobs that run at scheduled times or in response to SQL Server events. The SQL Server Agent subsystem loads and registers the SQL Server snap-ins so that you can run Windows PowerShell scripts.

For more information, see SQL Server Agent Subsystems.

Warning

Each SQL Server Agent job step launches a sqlps process which consumes approximately 20 MB of memory. Running large numbers of concurrent Windows PowerShell job steps can adversely impact performance.

Adding the SQL Server Snap-ins to Windows PowerShell

The sqlps utility is a Windows PowerShell mini-shell. Mini-shells have certain restrictions. For example, they are coded to load in one or more Windows PowerShell snap-ins, but users and scripts cannot add other snap-ins. If you require functionality not supported by a mini-shell, such as working with both the SQL Server snap-ins and the snap-ins from another product, you can add the SQL Server snap-ins directly into a Windows PowerShell environment.

Paste the following code into Notepad and save it as a ps1 script file on your computer, such as C:\MyFolder\InitializeSQLProvider.ps1:

# Add the SQL Server Provider.

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


# Set mandatory variables for the SQL Server provider
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

# Load the snapins, type data, format data
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location

You can then use the script to launch the Windows PowerShell environment in which the SQL Server snap-ins are loaded by using this command:

PowerShell -NoExit -Command "C:\MyFolder\InitializeSQLProvider.ps1"

The command can be run at a command prompt, from a desktop shortcut, or from the Run dialog box in the Start menu. By default, Windows PowerShell runs in Restricted mode, which does not support running scripts. For more information about enabling Windows PowerShell scripts, see Running Windows PowerShell Scripts.

Loading the SQL Server Management Objects in Windows PowerShell

The SQL Server provider automatically loads the SQL Server Management Object (SMO) assemblies. There are two cases where you may need to load the SMO assemblies directly:

  • If your script references a SMO object before the first command that references the provider or cmdlets from the SQL Server snap-ins.

  • You want to port SMO code from another language, such as C# or VB.Net, that does not use the provider or cmdlets.

The following code loads the SMO assemblies:

# Loads the SQL Server Management Objects (SMO)

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


$assemblylist = 
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility"


foreach ($asm in $assemblylist)
{
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

Push-Location
cd $sqlpsPath
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location