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

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