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