DaveWentzel.com            All Things Data

PowerShell Home

PowerShell Notes for the Administration of SQL Server
I've decided to start to try to learn Powershell to better my SQL Server administration skills.  I generally do as much as possible via sql scripts, batch files, and vbscript, but it never hurts to learn a new skill. 
 
PowerShell (PS from here out) requires .NET 2.0 framework.  You can launch Powershell by typing powershell at a command prompt.  Typing get-command will display all available commands. 
 
Starting the Shell
PowerShell from a prompt
or
sqlps (if running SQL Server 2008)
 
sqlps is the same as running:
powershell
add-pssnapin SqlServerProviderSnapin
add-pssnapin SqlServerCmdletSnapin
 
you can then immediately run:
export-console -Path whatever.psc1
to create a console file (shortcut) to launch PS with the snapins already loaded. 
 
Unrestricted Execution Policy
The first time you attempt to execute any PS script you'll likely get an error.  By default, PS scripts must be signed scripts.  You need to run the following:
set-executionpolicy unrestricted.  On 64-bit systems you'll likely have to run this twice since powershell.exe is installed as 32 and 64 bit binaries:
 
%SYSTEMROOT%\system32\windowspowershell\v1.0\powershell.exe
%SYSTEMROOT%\SysWOW64\windowspowershell\v1.0\powershell.exe
 
Note that this applies whether you are running PowerShell 1.0 or 2.0.  
 
The following command will list the execution policy on the machine
get-executionpolicy
 
PowerShell 2.0
As of this writing (20090210) 2.0 is in CTP.  I haven't tried it because it currently doesn't exist in a side-by-side fashion with 1.0.  No release date has been set either, although the assumption would be with Windows 7.  Some features I've heard about and am interested in:  PowerShell ISE (was Graphical PowerShell in earlier CTPs), background jobs, and remote execution without using PSEXEC, WMI, or any of the other kludges.  More on PowerShell 2.0
 
 
Basic Language Items
cmdlets and Functions
Other Topics
Tools Working with SQL Server
PoSH Community Extensions 2005 and 2008 Version Differences
Calling Functions
Passing Parameters into scripts, cmdlets, and functions
PowerShell UI SQL Server Management Objects (SMO)
  PowerShell Tools Profiler Integration
     
     
     
     
 
 
   
 
 
   
 
 
 
   
 
 
 
   
The BackTick Character `          
 
 
Useful Scripts
 
 

Add new comment