By: Diana Moldovan | 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.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips