Admittedly, SQL-Server 2005 is not up-to-date, but maybe I am not the only one still working with it and trying to use powershell with it. Powershell can access SQL-Server using the Invoke-Sqlcmd-Cmdlet which offers similiar possibilities like the command-line tool sqlcmd. Big difference is that powershell allows you to build much more sophisticated scripts.
It took me some time to find the information from different sources and make it work. This is why i gathered all necessary steps in this short article. Note, that this is not a tutorial about powershell and that I assume that you already have powershell installed on your pc which is the case for Vista and Windows7 operating systems.
Here we go:
- Download the PowerShell-extensions:
They are available from the Microsoft® SQL Server® 2008 R2 Feature Pack. This site contains a collection of many downloadable extensions for SQL Server 2008. Look for “Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2″, download the .msi and execute. Pay attention that you download the correct version (32-bit aka X86 or 64-bit aka X64).
- Install them:
After you executed the .msi-file you’ll find two assemblies in your SQL-Server subdirectory (probably: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist). These assemblies need to be installed by using the installutil. To do this open the command-line and execute the following commands (adjust the file paths according to your system):
C:\Windows\Microsoft.NET\Framework\v2.0.50727>installutil.exe -i "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSProvider.dll" C:\Windows\Microsoft.NET\Framework\v2.0.50727>installutil.exe -i "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.Management.PSSnapins.dll"
- Check that the SQL-Server CmdLets are properly installed:
You do so by executing “Get-PSSnapin -Registered”. In the displyed list you should see the following:
Name : SqlServerCmdletSnapin100 PSVersion : 2.0 Description : This is a PowerShell snap-in that includes various SQL Server cmdlets. Name : SqlServerProviderSnapin100 PSVersion : 2.0 Description : SQL Server Provider
- Check your powershell security restrictions:
They maybe don’t allow the execution of powershell scripts. To see your security configuration execute
If you get “restricted” you probably won’t be able to execute any powershell scripts until you lower the security restrictions. You can do so by executing
Set-ExecutionPolicy -ExecutionPolicy Unrestricted"
To do so, you must run powershell as admin.
- reference the SQL-Server CmdLets:
Before you can communicate to SQL-Server using powershell scripts you need to reference the SQL-Server CmdLets in your powershell session. You do so by executing:
Add-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction Continue Add-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction Continue
Note that you need to do this only once for the whole session. Trying to add them a second time results in an error.
- Make a test script to communicate with SQL-Server, e.g. you can query a table like this:
Invoke-Sqlcmd -ServerInstance MyInstance -Database MyDataBase -Query "SELECT * FROM MyTable"