How to Connect to SQL Server using Powershell

Hi Friends,

In this blog I’ll show you how to connect to SQL Server using Powershell Step-by-Step. There are basically two ways to connect to SQL Server,

A. Using sqlps utility from Powershell.exe

B. Using SSMS context menu option

 

Let’s review both:

1. Go to command prompt\cmd and type Powershell.exe & press enter.

clip_image001

This will open the Powershell console for you,

2. Now you can execute SQLPS to start SQLPS utility and load SQL specific snappins. You can also start SQLPS directly from your command prompt by writing sqlps.exe. To know more about the SQLPS utility and Snappins visit: http://www.sarabpreet.com/?p=203

clip_image002

3. Notice the change of drive letter from PS to PS SQLSERVER, so that means Powershell has a SQLServer Drive which is being used by SQLPS utility to connect to SQL Server. (More on this in future posts)

4. Now as you know we can browse all drive contents with a command dir which is an alias for Get-ChildItem So we can do the same in SQLSERVER drive also, since the behavior will remain same.

Note: I am using cd, dir which are alias to set-location & get-childitem respectively. This is just to make it easy to understand, going forward in the future posts we’ll be using Set-location cmdlet only.

5. Issue a dir command and you’ll get all objects you can access. For this post we’ll just concentrate on SQL which is there to connect to SQL Server Database Engine.

6. Now you can execute a cd command to go in SQL folder.

clip_image004

7. Again do a dir to get name of the Server. Once you get the Server name set-location to the server so that you can see all available SQL instances on the Server.

8. Finally we can see all available SQL instances on the server, On this machine I’ve got two SQL Server Instances installed one is DEFAULT and the other is a Named instance, named as PROD02.

clip_image006

9. Now let’s change the location by using either cd or set-location to connect to SQL Server Instance, here in this example I am connecting to PROD02 SQL Instance.

10. Once you are connected to SQL Instance you can see all contents (or I should say all objects under SQL instance) in the same hierarchy.

clip_image008

11. To connect to a database, all I need to do is just set-location to Databases folder and all available databases will be exposed as an object\folder. You can see all databases by the same old command dir. Smile

clip_image010

Wait a min, Where are my System Databases? (http://www.sarabpreet.com/?p=294)

Using SSMS

You can directly connect to this DB in Powershell if you access Powershell by the context menu of SSMS (SQL Server Management Studio).

1. Open SSMS and connect to the SQL Server Instance you wish to connect.

2. Now Right click on the DB you want to connect and choose Start Powershell.

clip_image011

Notice that the SQL Server automatically connected you to the DB you’ve chosen in SSMS.

clip_image012

Happy Learning!

Thanks,
Sarabpreet Singh

7 thoughts on “How to Connect to SQL Server using Powershell

  1. Pingback: Where are my system databases? » Powershell4SQL User Group

  2. Pingback: connecting to SQL with Powershell | SQL DBA learning curve

  3. Pingback: using powershell to select from Tables in database | SQL DBA learning curve

  4. Puneet

    I don’t have SSMS installed on my computer. Is there a way to connect to SQL server without installing the sql drivers? I am building some apps for operations guys and hence they want to use the app but don’t have SSMS installed on their machines

    Reply

Leave a Reply