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.

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

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.

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.

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.

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.

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.

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

Happy Learning!

Thanks,
Sarabpreet Singh