Relational Database Systems

Working with MS SQL Server using PowerShell

Microsoft has added new extensions to PowerShell for working with SQL Server and released it part of SQL Server 2008. Intent of this article is to guide you, how to use PowerShell to access data stored in SQL Server database. PowerShell is installed as part of SQL Server Management Toolset. That means as soon as SQL Server 2008 Management tool is installed, SQLPS.exe is available for running script. Provider for SQL Server creates a PowerShell drive that enables direct connetivity with SQL Server and enables SQL Server to be navigated like a file system.

How to use PowerShell for SQL Server

  • Start => Run => SQLPS.exe

  • There are four main directories. To list these directories name type ls as shown below:

 

  • SQL —  provides access to Database Engine, SQL Server Agent, Database Email etc.
  • SQLPolicy  — provides access to Policy Based Management System
  • SQLRegistration provides access to Registered Server as well Central Management Server feature
  • DataCollection — proves access to Data Collector Objects which is part of Management Data Warehouse
  • These directories are:
  • In this article, we are going to access Database Engine and run Simple Select Query. So we will perform following activities:
  • List all database Instances

Database Instances are under SQL Directory so lets make SQL as working directory by executing command (cd SQL) as shown below:

  • Connect to Instance that you would like to work with

If you know your SQL Server instance name then you may directly make that as working Directory otherwise you can list all SQL Server Instances available which can be done using ls command. Unlike other tool like BCP or OSQL, we do not need to write command with several parameter and corresponding value.

So, now lets connect to Instance as shown below:

  • At this point, you are connected to your Database Instance. SQL Server instance has multiple directories including Databases, Logins, Mail, ResourceGovernor etc. In order to view complete list, Run ls command as shown below:

 

  • List all databases available

Change Database folder as working directories and then list all available databases using ls command

  • Run select query. For example, I executed following query in AdventureWorks database

SELECT top 10 EmployeeID, Title FROM HumanResources.Employee

To use database : Run cd AdventureWorks

Now Run query using Invoke-Sqlcmd as shown below:

you can run help to display all available commands. Since list is long so it would be better if you can redirect help in text file. You can do so by running following command:

Remarks

PowerShell is very powerful for administrator and developer for automating Server Administration and Application Deployment. You may read more about it at http://msdn.microsoft.com/en-us/library/cc281954.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top