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.
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.
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:
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).
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.
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.
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.
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.
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.
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.
As mentioned, you can route the result into a file by specifying a full path of an output file.
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.
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.