Scripting out all SQL Linked servers on all servers with PowerShell

user_9582542 0
Viewed 385

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?

powershell-5.1 powershell
1 Answers
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 the DEFAULT\LinkedServers subfolder of the server item at hand, whose child items Get-ChildItem then enumerates and on each of which ForEach-Object Script invokes the .Script() method, using simplified syntax.

      • That is, ForEach-Object Script (% Script) is the equivalent of % { $_.Script() } in your question.

  • 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.
Get-Content Servers.txt | 
  ForEach-Object {
    Get-ChildItem "SQLSERVER:\SQL\$_\DEFAULT\LinkedServers" |
      ForEach-Object Script > "C:\Users\someuser\Documents\Powershell\OutputFiles\$_.sql"
  }
user_453750
Related