Iterating through SQL Servers and database objects with PowerShell

By:   |   Comments (5)   |   Related: > 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, December 2, 2014 - 6:11:59 PM - Chris Back To Top (35467)

None of this code works


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

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 (22755)

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 5, 2012 - 9:47:43 AM - dbaduck Back To Top (16793)

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 5, 2012 - 8:25:22 AM - Derek W Back To Top (16789)

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















get free sql tips
agree to terms