Access Sql Server From PowerShell

PowerShell is an interactive command-line shell. It is used for task automation and configuration management for many Microsoft platforms. Today, I am going to show you how to access Sql Server from PowerShell with sqlserver module. To enable PowerShell to access Sql Server, you need to get sqlserver module installed. You can follow the steps shown in this Microsoft document.

To confirm you have sqlserver module properly installed, you can run the following commands in PowerShell.

       import-module sqlserver

        get-module sqlserver

If everything goes well, you should see something similar as the screenshot shown below. In case there are any problems, you can refer to this nice blog.

ps_sql1

To further confirm you have all the Cmdlets installed, you can run the following command.

    get-command -module sqlserver

You should see a list of Cmdlets as shown in below:

ps_sql2.PNG

Now, you are ready to access Sql Server Database. You need to know the server and instance name and certainly you should have the right credentials. In this example, I will use the default instance name. To see a list of database, you can run the following commands subsequently (remember to replace ServerName with your server name).

 sqlserver:

 dir SQLSERVER:\\SQL\ServerName\Default\Databases

The first piece of command switches you to sqlserver module. The second piece of command shows you a whole list of the databases in your server. If commands run successfully, you should see  a similar screenshot as shown below. As you can see, the command shows you a big picture of the databases in your server.

ps_sql4

To select a specific database, you can add a select command followed by your database name.

 dir SQLSERVER:\\SQL\ServerName\Default\Databases | select DatabaseName

In this example, I select a database named ‘Test’. It only shows you the database name and no other database information such as size, recovery model, etc.

ps_sql3

Now, I want to create a table named ‘Persons’ in this database. After that, I want to insert data into this table. As you can see from the screenshot shown below, I need to invoke sqlcmd command followed by the quoted query and ServerInstance information. One thing I want to emphasize is that you need to specify the Database name by using three parts table name. After inserting the data, you can check if the insert was successful by running a select command. As you can see, it returns two rows as expected.

ps_sql7

You can also run DDL commands like Drop table. In the following screenshot, I first run a drop command and then try to run a select command. As expected, the select statement returns an “Invalid object name” error, confirming the table was dropped.

ps_drop.PNG

If you want to save time, you can run all the steps in one file. In this example, I created a sqlcmd.sql file which contains create, insert and select three steps as shown in below.

ps_sqlfile.PNG

To run the commands saved in the file, you can run the following command. If you want to save the result into a file, you can do so by specifying a full path of Out-File.

Invoke-sqlcmd -InputFile “full path” | Out-File -FilePath “full path”

The screenshot below shows the result without specifying the out file.

ps_runfile.PNG

As mentioned, you can route the result into a file by specifying a full path of an output file.

ps_filecomm.PNG

If you check the output file as shown below, you can easily see that data is separated by space which is exactly as we have seen before.

ps_outfile.PNG

Summary

In this blog, I have shown you how to use sqlserver module to access Sql Server from PowerShell. You can interactively access and modify Sql Server data from PowerShell command-line. In addition, you can stack all steps together in one file and run it with one piece of command for convenience. This module provides an alternative method to directly interact with Sql Server.

Thank you for your time reading my blog. If you find it helpful, please feel free to share it with your friends.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s