<译>通过PowerShell工具跨多台服务器执行SQL脚本

时间：2020-12-17 14:52:36

有时候，当我们并没有合适的第三方工具(大部分需要付费)去管理多台数据库服务器，那么如何做最省力、省心呢？！Powershell一个强大的工具，可以很方便帮到我们处理日常的数据库维护工作 。简单的几步搞定，下面介绍一个简单例子便于大家理解：

1，创建一个文件夹，如 D:\ExecScriptOnMultiServer

2，创建一个文本文件ServerList.txt，将所有需要管理的数据库实例名写到里面，格式如下：

    SQLInstanceName1
    SQLInstanceName2
    SQLInstanceName3
　 ...
然后保存到可以访问到的目录，如D:\ExecScriptOnMultiServer\ServerList.txt
 
3，将下面的Powershell脚本保存到目录 D:\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" > $OutputFile

FOREACH($server in GC "D:\ExecScriptOnMultiServer\ServerList.txt")

 {

    $server 

    "---------------------------------------------------------------------------------------------------------" >> $OutputFile

    $server >> $OutputFile

    "---------------------------------------------------------------------------------------------------------" >> $OutputFile

    invoke-sqlcmd -ServerInstance $server -query $ExecuteQuery -querytimeout 65534 | ft -autosize | out-string -width 4096 >> $OutputFile

 }

4，将你要在上述ServerList文件中的服务器实例执行的SQL语句，可先写到目录文件D:\ExecScriptOnMultiServer\SQLQuery.sql

例如：我要对所有实例下所有数据的的版本、SP和群集节点等信息收集，如下代码：

declare @Nodes Varchar(100)

set @Nodes=''

 if (SERVERPROPERTY('IsClustered') = 1)

begin

select @Nodes=@Nodes+ NodeName

+',' from sys.dm_os_cluster_nodes order by NodeName

set @Nodes=substring(@Nodes,0,LEN(@Nodes))

select

@Nodes as HostName,

SQLInstanceName = @@SERVERNAME,

'Yes' as IsClustered,

CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(50)) As ActiveNode,

SERVERPROPERTY('edition') As SQLEdition,

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(50))+' ('+ cast(SERVERPROPERTY('productversion') as varchar(50)) + ')' as SQLVersion

end

 else

begin

select @Nodes=CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(50))

select

@Nodes as HostName,

SQLInstanceName = @@SERVERNAME,

'No' as IsClustered,

SERVERPROPERTY('edition') As SQLEdition,

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(50))+' ('+ cast(SERVERPROPERTY('productversion') as varchar(50)) + ')' as SQLVersion

end

5，开启Window PowerShellISE程序，注意要用管理员身份启动，打开Powershell脚本ExecuteQueryOnMultiServers.ps1，点击执行。

执行后结果保存如代码中标注，位置在D:\ExecScriptOnMultiServer\QueryOutput.txt　

<译>通过PowerShell工具跨多台服务器执行SQL脚本

６，或者直接在PowerShell命令行执行，

PS C:\>D:\ExecScriptOnMultiServer\ExecuteQueryOnMultiServers.ps1

小结：

PowerShell工具是非常强大的工具，特别在跨多服务器的维护中，更能体现高效。本文只是一个影子，有兴趣的朋友可以参考下面的资料学习:

PowerShell Tips

本文转自: http://www.sqlservercentral.com/scripts/powershell/129948/

相关文章