Advertisements
Home > Powershell, SQL Server > SQL Sever – PowerShell for Executing a SQL Query on Multiple Servers

SQL Sever – PowerShell for Executing a SQL Query on Multiple Servers

This is 50th Article on Bi-BigData.com and we are getting great response from the community. This article will be covering how to use PowerShell and SMO to connect multiple SQL Server instances and execute a pre defined SQL command against all of them. This is applicable when we have same database available on multiple SQL Server Instances where you want to execute same SQL query. We are going to use the Invoke-Sqlcmd cmdlet to achieve our mission.

To achieve this we need to identify the available SQL Server instances to run your SQL Query on. You can create a text file in C:\PowerShell folder called ServerInstances.txt and put every instance name with line delimiter. For example:

SQL2012
MyDomain\SQL2008R2
PRODUCTION\SQL2012

We can use Inline SQL or use SQL file as parameter to Invoke-Sqlcmd cmdlet to make it more dynamic.

#This PowerShell Scrip is well-suited with PowerShell V3.0
#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#get all the instances and temporarily store them in a variable
$ServerInstences = Get-Content "C:\PowerShell\ServerInstances.txt"

#the global query we want to execute.
#this can be *.SQL  file which can be passed in Invoke-Sqlcmd
$SQLQuery = @"
CREATE TABLE [dbo].[PS_Table]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_PS_Table] PRIMARY KEY CLUSTERED ([ID] ASC)
)
"@

#he database we want to execute it against, regardless of the instance
$DBName = "MyDatabase"

#iterating through all instances.
$ServerInstences |
ForEach-Object {
#For each instance, we create a new SMO server object
$ServerObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
#use the Invoke-Sqlcmd cmdlet to execute the query
#we are passing in the pipeline is the instance name, which is $_
Invoke-Sqlcmd `
-ServerInstance $_ `
-Database $DBName `
#-InputFile "C:\PowerShell\SQLScript.sql"
-Query $SQLQuery
} 

Hope this helps.

Thanks

Sandip

Advertisements
  1. vps
    March 14, 2013 at 8:52 pm

    I think your blog is great. You write about very interesting things. Thanks for all the tips and information.

  2. vps
    March 14, 2013 at 10:10 pm

    This post is great. I realy like it!

  3. johnny
    March 15, 2013 at 4:49 pm

    This blog is amazing. I realy like it!

  4. March 25, 2013 at 6:38 pm

    My spouse and i have been quite satisfied that Raymond managed to finish up his analysis through your ideas he was given out of the web page. It is now and again perplexing to simply be giving for free helpful hints that others could have been trying to sell. And we also fully grasp we have got the writer to appreciate for that. All the illustrations you have made, the easy blog navigation, the relationships your site assist to foster – it’s got mostly superb, and it is aiding our son in addition to the family do think this content is interesting, which is certainly exceedingly mandatory. Thanks for the whole lot!

  5. March 27, 2013 at 8:47 am

    I’ve been browsing online greater than 3 hours these days, yet I never discovered any attention-grabbing article like yours. It is lovely worth enough for me. In my opinion, if all web owners and bloggers made good content as you probably did, the internet might be a lot more useful than ever before.

  6. March 28, 2013 at 7:56 am

    Can I simply say what a reduction to seek out someone who actually is aware of what theyre talking about on the internet. You undoubtedly know easy methods to deliver an issue to light and make it important. Extra individuals need to learn this and understand this side of the story.

  7. April 7, 2013 at 11:37 pm

    The following time I learn a weblog, I hope that it doesnt disappoint me as a lot as this one. I mean, I do know it was my choice to learn, but I really thought youd have one thing fascinating to say. All I hear is a bunch of whining about one thing that you can repair in case you werent too busy searching for attention.

  8. April 8, 2013 at 2:30 pm

    It is best to take part in a contest for probably the greatest blogs on the web. I will suggest this website!

  9. April 12, 2013 at 6:30 am

    Hello.This post was really fascinating, particularly because I was looking for thoughts on this topic last couple of days.

  10. April 12, 2013 at 7:03 am

    Somebody essentially help to make seriously posts I would state. This is the very first time I frequented your web page and thus far? I amazed with the research you made to create this particular publish extraordinary. Magnificent job!

  11. April 18, 2013 at 5:44 am

    I just want to tell you that I am newbie to blogging and seriously enjoyed your web-site. Likely I’m going to bookmark your website . You surely come with great well written articles. Appreciate it for sharing your blog site.

  12. Naga
    June 30, 2016 at 8:23 am

    Iam getting below error message..Any help please

    Missing expression after unary operator ‘-‘.
    At D:\DBA\check_policy.ps1:32 char:2
    + – <<<< Query $SQLQuery
    + CategoryInfo : ParserError: (-:String) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingExpressionAfterOperator

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: