PowerShell –SQL Automate Tutorial-1

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: Basic of PowerShell before diving into SQL automation with the help of PS

Scenario: PS is most powerful and very helpful tool for windows and SQL server administrator and automation. So its todays need to learn at least basic of some PS cmdlets.

Note: Whether PS scripts are getting executed through CMD or On PS window itself we need to first check the Execution policy.

The Default Execution Policy is set to restricted; you can see it by typing:

Get-ExecutionPolicy

If you get restricted policy then change it to RemoteSigned to run the PS command.

Open the PS window (Run as Administrator) for both 32-64 bits and type below command:

For Windows 7, Windows 8, Windows Server 2008 R2 or Windows Server 2012, and so on run the following commands as Administrator:

Set-ExecutionPolicy RemoteSigned

Or you can bypass the execution policy

Example: powershell.exe  -noprofile  -executionpolicy  bypass  -file .\script.ps1

If you do not change to RemoteSigned you will be welcomed with below error message.

Example.ps1 cannot be loaded because the execution of scripts is disabled on this system.

Okay cool!!

Let’s start with code and explanation:

  • Step 1:- Create a simple text file.
  • Step 2:- Write any PowerShell commands

E.g. Get-Process | Sort-Object ID

Note: Get-Process using this you can get a list of processes; “Sort-Object ID” for the list will be sorted by ID. Use pipe symbol for separation “|”

  • Step 3:- Rename extension .txt to .PS1 extension (Create at some location with FirstPowershell.PS1 e.g. E:\Dharmesh\PSTutorial \getprocess.ps1)
  • Step 4:- Open PowerShell and Run E:\Dharmesh\PSTutorial \getprocess.ps1

Output: ID column (object) is sorted in acceding order by default. You can put more than one object (column) in sorting.

Getprocess

Happy Learning!!