I need to export script of all my linked server in my environment to a file for each server.
The following command below successfully scripts out all the linked servers on ServerName1
get-childitem | %{$_.script()} >> C:\Users\someuser\Documents\Powershell\OutputFiles\ServerName1.sql
The issue is that I have 35 servers and in order for this command to work I need to change directories of each server.
In other words the command need to be run as follows for each server
PS SQLSERVER:\SQL\ServerName1\DEFAULT\LinkedServers> get-childitem | %{$_.Script()} >> C:\Users\someuser\Documents\Powershell\OutputFiles\ServerName1.sql
How could I loop a command that runs from different paths based on Server Name and names the file same as the server name?
CodePudding user response:
Get-ChildItem -Literal SQLSERVER:\SQL |
ForEach-Object {
$serverName = $_.Name
Get-ChildItem -LiteralPath (Join-Path $_.PSPath DEFAULT\LinkedServers) |
ForEach-Object Script > "C:\Users\someuser\Documents\Powershell\OutputFiles\$serverName.sql"
}
Get-ChildItem
-LiteralPath SQLSERVER:\SQL
is assumed to return items that each represent a server [THIS MAY NOT BE TRUE - SEE BELOW.]Each resulting item is then processed in the
ForEach-Object
script block:Join-Path
is used to construct the full path to theDEFAULT\LinkedServers
subfolder of the server item at hand, whose child itemsGet-ChildItem
then enumerates and on each of whichForEach-Object Script
invokes the.Script()
method, using simplified syntax.- That is,
ForEach-Object Script
(% Script
) is the equivalent of% { $_.Script() }
in your question.
- That is,
Note that
>
is enough to capture all output from the pipeline in the target file;>>
is only needed if you want to append to a preexisting target file.
You state that Get-ChildItem -LiteralPath SQLSERVER:\SQL
does not list all servers, and that you want to provide the list of server names via a text file:
Save the list of server names to
Servers.txt
, with each name on its own line.Then try the following:
- Note: The assumption is that, for a given server X, accessing path
SQLSERVER:\SQL\X\DEFAULT\LinkedServers
implicitly connects to it.
- Note: The assumption is that, for a given server X, accessing path
Get-Content Servers.txt |
ForEach-Object {
Get-ChildItem "SQLSERVER:\SQL\$_\DEFAULT\LinkedServers" |
ForEach-Object Script > "C:\Users\someuser\Documents\Powershell\OutputFiles\$_.sql"
}