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

• Windows PowerShell 1.0, if PowerShell is not already present on your computer.
  
• The SQL Server PowerShell snap-ins. The snap-ins are dll files that implement two types of PowerShell support for SQL Server:
  
  • A set of SQL Server PowerShell 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 PowerShell 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 PowerShell sessions that include the SQL Server snap-ins.
  

The SQL Server 2008 version of SQL Server Management Studio supports starting PowerShell sessions from the Object Explorer tree. The SQL Server 2008 version of SQL Server Agent supports PowerShell job steps.

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

• Manually download and reinstall 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, PowerShell 1.0 exists in the operating system but is disabled by default. You can re-enable PowerShell from Windows Server 2008.
  

You must use the SQL Server 2008 client components to run SQL Server PowerShell. SQL Server PowerShell can connect to instances of SQL Server 2008, SQL Server 2005, or SQL Server 2000. 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 SQL Server PowerShell connects to SQL Server 2005 and SQL Server 2000, it is limited to the functionality available in those versions of SQL Server.

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

• Interactively run PowerShell commands.
  
• Run 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 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 PowerShell scripts, see Running Windows PowerShell Scripts.

For more information, see sqlps Utility.

You can load the SQL Server PowerShell snap-ins directly into the PowerShell environment. You must first create a PowerShell console file.

1. Open a command prompt window.
   
2. At the command prompt, type powershell
   
3. At the PowerShell prompt, run these commands, replacing C:\MyFolder with the name of a folder on your computer:
   
   cd C:\MyFolder
   add-pssnapin SqlServerProviderSnapin100
   add-pssnapin SqlServerCmdletSnapin100
   Export-Console -Path MySQLConsole.psc1

This creates a MySQLConsole.psc1 file in C:\MyFolder. You can then use the console file to launch the PowerShell environment in which the SQL Server PowerShell snap-ins are loaded by using this command:

powershell.exe  -psconsolefile C:\MyFolder\MySQLConsole.psc1

The command can be run at a command prompt, from a desktop shortcut, or from the Run dialog box in the Start menu.

You can start 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 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 AdventureWorks database object in Object Explorer and select Start PowerShell, the PowerShell path is set to the following:

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

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 PowerShell subsystem supports job steps that execute PowerShell scripts. You can code 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 PowerShell subsystem loads and registers the SQL Server PowerShell snap-ins so that you can run SQL Server PowerShell scripts.

For more information, see SQL Server Agent Subsystems.

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

Concepts

Other Resources

Help and Information