mssqltips logo

Iterating through SQL Servers and database objects with PowerShell

By:   |   Updated: 2012-04-05   |   Comments (5)   |   Related: More > PowerShell

Problem

I've read several articles on MSSQLTips about executing queries against multiple servers or databases. I found advice about using the Local Servers Group to run a query against more than one SQL instance, about Central Management Server, about sp_MSforeachdb or about using the Management Studio query editor in SQLCMD mode. What about PowerShell? Can I use PowerShell to run queries against several instances, databases and database objects?

Solution

Yes.  Let's work through some examples.  I've run the examples I'm presenting here in an environment consisting of 3 servers grouped in a domain. Each server hosts a single SQL Server instance - there is a SQL 2008 instance, a SQL 2005 instance and a SQL 2008 R2 instance. I'm connecting to these servers using a domain account which is in sysadmin role on all the machines. In order to set up PowerShell for working with SQL Server, I've added the SqlServerCmdletSnapin100 and SqlServerProviderSnapin100 cmdlet's to my PowerShell profile.

Let's "translate" into PowerShell the examples I've set up for you here.

Collecting information about the SQL Servers in your network

    $Servers = "SQL2005", "SQL2008", "SQL2008R2"
    $Query = "SELECT SERVERPROPERTY('ServerName') AS ServerName
                ,SERVERPROPERTY('ProductVersion') AS ProductVersion
                ,SERVERPROPERTY('ProductLevel') AS ProductLevel
                ,SERVERPROPERTY('Edition') AS Edition
                ,SERVERPROPERTY('EngineEdition') AS EngineEdition;"
    $Servers | ForEach-Object{
 $server = "$_";
 Set-Location SQLSERVER:\SQL\$server Invoke-Sqlcmd -Query $Query -ServerInstance $server;
 }
    

I'm using the Invoke-Sqlcmd cmdlet and ForEach-Object to run the query against each server from the list. Instead of reading each server from the list, you can provide a text input file which contains all the server names and read the content of that file. Here is the expected result:

     ServerName     : SQL2005
    ProductVersion : 9.00.5000.00
    ProductLevel   : SP4
    Edition        : Enterprise Edition
    EngineEdition  : 3
    ServerName     : SQL2008
    ProductVersion : 10.0.4000.0
    ProductLevel   : SP2
    Edition        : Developer Edition
    EngineEdition  : 3
    ServerName     : SQL2008R2
    ProductVersion : 10.50.1600.1
    ProductLevel   : RTM
    Edition        : Enterprise Edition (64-bit)
    EngineEdition  : 3
    

Disabling the SQL Server guest user in each database for each server

This time the query will come from an input file - here is its content:

    REVOKE CONNECT FROM GUEST
   GO
    

The script will be executed against each database of each SQL instance.

    $Servers = "SQL2005", "SQL2008", "SQL2008R2" 
    $InputFile = "C:\MyLocation\RevokeGuest.sql";
    $Servers | ForEach-Object{
 $server = "$_";
 Set-Location SQLSERVER:\SQL\$server\DEFAULT\DATABASES;
 Get-ChildItem | ForEach-Object{
 $Db = $_.Name;
 Invoke-Sqlcmd -SuppressProviderContextWarning -InputFile $InputFile -Database $Db;
     }
    }
    

Use the "SuppressProviderContextWarning" parameter to remove the warning message "WARNING: Using provider context. Server = MyServer". Otherwise this message will appear on your screen, once for every database processed.

Collecting information about the databases from a SQL Server instance

Over time, you'll add complexity to your admin and maintenance scripts. However, the "core" of the script may be straightforward. The next example shows you how easy will be to collect information about the databases from a SQL instance and to export this information in a csv file:

    $OutFile = "C:\MyLocation\OutFile.csv"
    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES
    Get-ChildItem | Select-Object Name, Status | Export-csv -NoTypeInformation $OutFile
    

I'm using the "NoTypeInformation" parameter so that the .NET object type won't be mentioned in the output file.

Next Steps
  • As you have a need to address problems in your environment, consider PowerShell as an alternative.
  • Check out all of the PowerShell tips on MSSQLTips.


Last Updated: 2012-04-05


get scripts

next tip button



About the author
MSSQLTips author Diana Moldovan Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, December 02, 2014 - 6:11:59 PM - Chris Back To Top

None of this code works


Friday, August 23, 2013 - 1:13:51 PM - Ed Back To Top

Actually, I did load sql server snapins: when I run this script I get:

Add-PSSnapin : Cannot add Windows PowerShell snap-in SqlServerCmdletSnapin100 because it is already added. Verify the name of the snap-in and try again...

Add-PSSnapin : Cannot add Windows PowerShell snap-in SqlServerProviderSnapin100 because it is already added. Verify the name of the snap-in and try again...

and eventually:

Set-Location : A positional parameter cannot be found that accepts argument 'Invoke-Sqlcmd'...

 


Tuesday, March 12, 2013 - 2:28:14 PM - steve sofar Back To Top

Hello

Very interesting code

I'm newby in Powershell

Is there a way to define a list of SQL Servers in a txt file eg : SQL-Servers.txt  and specifying the SQLServer the SQL authentication like following And to loop into it to execute your magic powershell script

eg :SQL-Servers.txt

SERVER1 sa only4admin

SERVER2 sa Power

SERVER3 test dbatest

thanks a lot


Thursday, April 05, 2012 - 9:47:43 AM - dbaduck Back To Top

This just means that you do not have the SQL Server Providers loaded for PowerShell.  You can see if you have them installed by using:

Get-PSSnapin -Registered

See if you have SqlServerProviderSnapin100 and SqlServerCmdletSnapin100 in the list.  If you do then you can load them.

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

Then you can run the scripts above.  If you do not have them loaded you need to go to http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16978 and download the SQLCLR Types, Shared Management Objects, and PowerShell Extensions and load them in that order and then add the snapins as described above and then you will be able to run the commands above.

Hope that helps.


Thursday, April 05, 2012 - 8:25:22 AM - Derek W Back To Top

I get the following error when I try to use it:

Set-Location : A positional parameter cannot be found that accepts argument 'Invoke-Sqlcmd'.At C:\Documents and Settings\d_whart\Local Settings\Temp\a16ed35c-359e-49d6-9ef1-70a9afcfbebf.ps1:21 char:14+ Set-Location <<<< SQLSERVER:\SQL\$server Invoke-Sqlcmd -Query $Query -ServerInstance $server;+ CategoryInfo : InvalidArgument: (:) [Set-Location], ParameterBindingException+ FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.SetLocationCommand



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools