< translate > Execute SQL scripts across multiple servers with PowerShell tools

Source: Internet
Author: User

Sometimes, when we do not have the right third-party tools (most need to pay) to manage more than one database server, then how to do the least effort, worry about it?! PowerShell is a powerful tool that can easily help us with daily database maintenance work. Just a few simple steps, here's a simple example for you to understand:

1. Create a folder, such as D:\ExecScriptOnMultiServer

2, create a text file ServerList.txt, write all the database instance names that need to be managed inside, in the following format:

SQLInstanceName1 SQLInstanceName2 SQLInstanceName3 ... ..Then save to a directory that you can access, such as D:\ExecScriptOnMultiServer\ServerList.txt 3, Save the following PowerShell script to the directoryD:\ExecScriptOnMultiServer\ExecuteQueryOnMultiServers.ps1
##Save The below PowerShell script in folder "D:\ExecScriptOnMultiServer\ExecuteQueryOnMultiServers.ps1"$QueryPath="D:\ExecScriptOnMultiServer\SQLQuery.sql"$OutputFile="D:\ExecScriptOnMultiServer\QueryOutput.txt"$ExecuteQuery= Get-content-path$QueryPath| out-string"Results--> ' R ' n ' r ' n">$OutputFileFOREACH($server inchGc"D:\ExecScriptOnMultiServer\ServerList.txt") {    $server     "---------------------------------------------------------------------------------------------------------">>$OutputFile    $server>>$OutputFile    "---------------------------------------------------------------------------------------------------------">>$OutputFileInvoke-sqlcmd-serverinstance$server-query$ExecuteQuery-querytimeout 65534 | Ft-autosize | Out-string-width 4096 >>$OutputFile }

4, the SQL statement that you want to execute on the server instance in the ServerList file above can be written to the directory file first D:\ExecScriptOnMultiServer\SQLQuery.sql

For example, I want to collect information about the version, SP, and cluster nodes of all the data under all instances, such as the following code:

Declare @Nodes Varchar( -)Set @Nodes="' if(Serverproperty ('isclustered')= 1)beginSelect @Nodes=@Nodes+NodeName+','  fromSys.dm_os_cluster_nodesOrder  byNodeNameSet @Nodes=substring(@Nodes,0,LEN(@Nodes))Select @Nodes  asHostName, Sqlinstancename= @ @SERVERNAME,'Yes'  asisclustered,CAST(Serverproperty ('Computernamephysicalnetbios') as VARCHAR( -)) asActivenode,serverproperty ('Edition') asSqledition, Case    when cast(Serverproperty ('productversion') as varchar) like '8.%'  Then 'SQL2000'        when cast(Serverproperty ('productversion') as varchar) like '9.%'  Then 'SQL2005'        when cast(Serverproperty ('productversion') as varchar) like '10.0%'  Then 'SQL2008'        when cast(Serverproperty ('productversion') as varchar) like '10.50.%'  Then 'sql2008r2'        when cast(Serverproperty ('productversion') as varchar) like '11.%'  Then 'SQL2012'   when cast(Serverproperty ('productversion') as varchar) like '12.%'  Then 'SQL2014'       ELSE 'SQL7.0' END +' '+  cast(Serverproperty ('ProductLevel') as varchar( -))+' ('+ cast(Serverproperty ('productversion') as varchar( -))+ ')'  assqlversionEnd Else beginSelect @Nodes=CAST(Serverproperty ('Computernamephysicalnetbios') as VARCHAR( -)) Select @Nodes  asHostName, Sqlinstancename= @ @SERVERNAME,'No'  asIsclustered,serverproperty ('Edition') asSqledition, Case    when cast(Serverproperty ('productversion') as varchar) like '8.%'  Then 'SQL2000'        when cast(Serverproperty ('productversion') as varchar) like '9.%'  Then 'SQL2005'        when cast(Serverproperty ('productversion') as varchar) like '10.0%'  Then 'SQL2008'        when cast(Serverproperty ('productversion') as varchar) like '10.50.%'  Then 'sql2008r2'        when cast(Serverproperty ('productversion') as varchar) like '11.%'  Then 'SQL2012'   when cast(Serverproperty ('productversion') as varchar) like '12.%'  Then 'SQL2014'       ELSE 'SQL7.0' END +' '+  cast(Serverproperty ('ProductLevel') as varchar( -))+' ('+ cast(Serverproperty ('productversion') as varchar( -))+ ')'  assqlversionEnd

5, open the window powershellise program, note to start with the administrator, open PowerShell script EXECUTEQUERYONMULTISERVERS.PS1, click to execute.

After execution, the result is saved as a callout in the code, and the location is D:\ExecScriptOnMultiServer\QueryOutput.txt

6, or execute directly on the PowerShell command line,

PS C:\>D:\EXECSCRIPTONMULTISERVER\EXECUTEQUERYONMULTISERVERS.PS1

Summary:

PowerShell tools are very powerful tools, especially in the maintenance of multi-server, more efficient. This article is just a shadow, interested friends can refer to the following information study:

PowerShell Tips

This article transferred from: http://www.sqlservercentral.com/scripts/powershell/129948/

< translate > Execute SQL scripts across multiple servers with PowerShell tools

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.