PowerShell has a lot to offer with its capabilities as a shell (for doing things) and a scripting language (for programming), therefore, it can be used for particularly every management task offering a great tool for both database administrators and developers.
This blog will cover some basics of using PowerShell with SQL Server, let’s get progressing…
To use the PowerShell with SQL Server, firstly you need to import the SQL PowerShell module, without the module you wouldn’t be able to interact/connect with the SQL Server.
I always find get-help cmdlet in Powershell being a great tool that displays information about PowerShell concepts and commands, including cmdlets, functions, scripts and others. To get help for a PowerShell command, type Get-Help followed by the command name, such as: Get-Help *Import*. Further information available on: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/get-help?view=powershell-6
Import the SQLPS module:
At the SQL Server directory type dir to list all the contents.
We want to navigate through the SQL directory: CD ./SQL then Dir to list the contents.
Navigate to the Machine name: CD ./NOTUKDEV05 and list the contents.
you will see the instance names on your environment, default (MSSQLSERVER)
Navigate to the instance and view the contents belonging to the SQL server instance:
Now you can start querying the SQL Server:
Let’s check the version of the SQL Server and the current date (date function) using invoke-sqlcmd:
Let’s query the sys.databases system view to get information about databases:
You now know the basics of accessing and querying SQL Server with PowerShell, and with the capabilities, PowerShell has to offer you can use it to manage multiple SQL Server at once or even retrieve information for multiple SQL Servers through one script. This saves time and reduces the number of times you have to repeat the same tasks over and over again.