SQL Server 2008 introduces support for Windows PowerShell. PowerShell provides the ability to produce feature-rich tools that allow you to administer servers and improve the functionality within SQL Server and the ability to build robust administration scripts.
Windows PowerShell is command shell and scripting language that provides a command line environment for interactive exploration and administration of computers. In addition, it provides an opportunity to script these commands so that we can schedule and run these scripts multiple times.
In addition, it provides an opportunity to script these commands so that we can schedule and run these scripts multiple times. Windows PowerShell depends on .NET framework 2.0.
- The PowerShell language supports more complex logic than Transact-SQL, giving SQL Server administrators the ability to build robust administration scripts.
- The SQL Server provider enables a simple navigation mechanism similar to file system paths.
- The SQL Server cmdlets support actions such as running a sqlcmd script containing Transact-SQL or XQuery statements.
- The sqlps utility starts a PowerShell session with the SQL Server PowerShell provider and cmdlets loaded and registered.
The PowerShell Cmdlets with Auxiliary Tasks:
- Decode-SqlName: Takes an encoded SQL Server identifier and returns the original identifier.
- Encode-SqlName: Takes a SQL Server identifier, and reformats all characters not supported by PowerShell with a representation that will work in PowerShell.
- Invoke-Sqlcmd: Runs scripts that contain statements from Transact-SQL and Xquery and commands supported by the sqlcmd utility.
- Convert-UrnToPath: Converts SMO URN strings to PowerShell paths.
The SQL Server PowerShell Hierarchy
Products whose data or object models can be represented in a hierarchy use PowerShell providers to expose the hierarchies. The hierarchy is exposed by using a drive and path structure similar to what the Windows file system uses.
Each PowerShell provider implements one or more drives. Each drive is the root node of a hierarchy of related objects. The SQL Server PowerShell provider implements a SQLSERVER: drive. The SQLSERVER: drive has three folders. Each folder and its subfolders represent the set of objects that can be accessed by using a SQL Server management object model. When you are focused on a node in a path that starts with one of these folders, you can use the methods from the associated object model to perform actions on the object that is represented by the node. PowerShell also lets users define virtual drives, which are referred to as PowerShell drives or PSDrives. These virtual drives map over the starting nodes of a path statement. They are typically used to shorten paths that are typed frequently. The SQL Server PowerShell provider implements three PowerShell drives that represent the three main folders on the SQLSERVER: drive.
Use the sqlps utility
- Click Start | Run.
- The Run dialog box appears. Type sqlps and then click OK.
- The Microsoft SQL Server PowerShell window opens.
- The sqlps utility starts the PowerShell environment (PowerShell.exe) with the SQL Server PowerShell snap-ins loaded and registered.
Use the Invoke-Sqlcmd cmdlet
- Type get-help Invoke-Sqlcmd -Full and then press ENTER.
- Type Invoke-Sqlcmd -Query “SELECT GETDATE() AS TimeOfQuery;” -ServerInstance “MyComputer\MyInstance” and then press ENTER.
- Type Invoke-Sqlcmd -QueryFromFile “C:\MyFolder\SQLCmd.sql” | Out-File -filePath “D:\SQLData\TestSQLCmd.rpt” and then press ENTER.
Use the SQL Server cmdlets
*** If, Power shell is not installed, Download and Install Microsoft PowerShell “WindowsXP-KB926139-x86-ENU.exe” from http://download.microsoft.com