PowerShell in SQL Server 2008 – Part III

Hello Folks,

You must have seen in my previous blog on the commands, operators and variables in Windows PowerShell. If not, you can also refer to the link;

Is it so easy to Create and Run the Scripts in Windows PowerShell??? Well it’s not.

This blog post mainly focuses on the problems that may arouse while Creating or Running the Scripts.

Creation of Scripts:

  • How to create directory or file in a PowerShell?

For creating a directory-

PS C:\> new-item -name “new folder” -type directory

1_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

For creating a file-

PS C:\> new-item -name “abc.text” -type file

2_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

  • How to create script in a PowerShell?

As you have learnt from above that how to create directory or file, so now it would be much easy for you to understand the creation of script:

PS C:\> new-item -name “data.ps1” -type file

3_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

So, the PowerShell script is being created with the name “data.ps1” where .ps1 is an extension name for PowerShell script.

  • How to insert the content in a script?

For the insertion of content in a script file do the following:

Select the file from the location and double-click it

4_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

 

  • Write the command that you want in the script.

5_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

  • Save it.

6_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

Running of Scripts:

Just write the following command to run the script:

PS C:\> .\data.ps1

7_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

Now what’s that?

The error is “File C:\Users\Piyush\data.ps1 cannot be loaded because the execution of scripts is disabled on this system.”

Let’s check the default Windows PowerShell execution policy:

8_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

Here, the default execution policy is Restricted which will prevents all the scripts from running, including scripts that you write in the local computer.

So we have to change the execution policy to RemoteSigned, which will allow the scripts to be run on the PowerShell. For this follow the 2-step carefully:

  • Open the “Windows PowerShell Run as Administrator” from the Start menu:

9_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

  • Change the Execution Policy:

10_SQL_Server_PowerShell_in_SQL_Server_2008_Part_III

Now the Execution Policy is being set and you can get back to your Windows PowerShell screen and then run the command again:

PS C:\> .\data.ps1

So this is how you should create and run the scripts.

Well this is enough for this post. Hope you like it 🙂

In the next blog post I would like to tell you more about this Microsoft’s amazing feature Windows PowerShell….So Stay Tuned!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

2 Comments on “PowerShell in SQL Server 2008 – Part III”

  1. Well, for me powershell scripting was great leap after old command shell, bt at times i still feel its not used that much in mainstream. Neways thanx for the info.

  2. Windows PowerShell is basically being built to provide Linux administrators who are very much addicted to scripting kind of stuffs in the Microsoft Windows platform. Since its being a new feature, so it might take a bit of more time to get recognized or to be on the tips…

Leave a Reply

Your email address will not be published. Required fields are marked *