Scripting SQL Linked Servers using PowerShell Script

2013-08-29
Scripting out all objects from a SQL server is a way of daily backup for a DBA’s daily job. Following are the scripts I use to take care of it using PowerShell script.
<span style="color: #008000;"><#</span><span style="color: #008000;">
.Synopsis
   Script out linked servers on a SQL server
.DESCRIPTION
   Script out linked servers on a SQL server
.EXAMPLE
   Script-SQLLinkedServers -server "Test" -outputpath "C:\Databases\Test\LinkedServers" 
</span><span style="color: #008000;">#>
</span><span style="color: #0000ff;">function </span><span style="color: #5f9ea0;">Script-SQLLinkedServers</span><span style="color: #000000;">
{    
    </span><span style="color: #0000ff;">Param</span><span style="color: #000000;">
    (
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> sql server name to script</span><span style="color: #000000;">        
        [</span><span style="color: #008080;">string</span><span style="color: #000000;">]
        </span><span style="color: #800080;">$server</span><span style="color: #000000;">,
        </span><span style="color: #008000;">#</span><span style="color: #008000;"> path to store the sql scripts</span><span style="color: #000000;">        
        [</span><span style="color: #008080;">string</span><span style="color: #000000;">]
        </span><span style="color: #800080;">$outputpath</span><span style="color: #000000;">
    )

        [</span><span style="color: #008080;">reflection.assembly</span><span style="color: #000000;">]::</span><span style="color: #8b4513;">LoadWithPartialName</span><span style="color: #000000;">(</span><span style="color: #800000;">"</span><span style="color: #800000;">Microsoft.SqlServer.Smo</span><span style="color: #800000;">"</span><span style="color: #000000;">) | </span><span style="color: #5f9ea0; font-weight: bold;">Out-Null

</span><span style="color: #0000ff;">        if</span><span style="color: #000000;"> (</span><span style="color: #ff0000;">-not</span><span style="color: #000000;"> (</span><span style="color: #5f9ea0; font-weight: bold;">Test-Path </span><span style="color: #800080;">$outputpath</span><span style="color: #000000;">))
        {
            </span><span style="color: #5f9ea0; font-weight: bold;">New-Item </span><span style="color: #5f9ea0; font-style: italic;">-Path </span><span style="color: #800080;">$outputpath </span><span style="color: #5f9ea0; font-style: italic;">-ItemType </span><span style="color: #800000;">directory</span><span style="color: #000000;">
        }

        </span><span style="color: #800080;">$srv </span><span style="color: #ff0000;">= </span><span style="color: #5f9ea0; font-weight: bold;">New-Object </span><span style="color: #800000;">Microsoft.SqlServer.Management.Smo.Server</span><span style="color: #000000;">(</span><span style="color: #800000;">"</span><span style="color: #800000;">$server</span><span style="color: #800000;">"</span><span style="color: #000000;">) 
        </span><span style="color: #800080;">$db </span><span style="color: #ff0000;">= </span><span style="color: #5f9ea0; font-weight: bold;">New-Object </span><span style="color: #800000;">Microsoft.SqlServer.Management.Smo.Database
</span><span style="color: #800080;">        $scrp </span><span style="color: #ff0000;">= </span><span style="color: #5f9ea0; font-weight: bold;">New-Object </span><span style="color: #800000;">Microsoft.SqlServer.Management.Smo.Scripter</span><span style="color: #000000;">(</span><span style="color: #800080;">$srv</span><span style="color: #000000;">) 
        </span><span style="color: #800080;">$scrp</span><span style="color: #000000;">.Options.ScriptDrops </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$FALSE
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.WithDependencies </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$TRUE
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.IncludeHeaders </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.AppendToFile </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$False
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.ToFileOnly </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.ClusteredIndexes </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.DriAll </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.Indexes </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $scrp</span><span style="color: #000000;">.Options.Triggers </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$True
</span><span style="color: #800080;">        $lsvrs </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$srv</span><span style="color: #000000;">.LinkedServers

        </span><span style="color: #0000ff;">foreach</span><span style="color: #000000;">(</span><span style="color: #800080;">$lsvr</span><span style="color: #0000ff;">in</span><span style="color: #800080;">$lsvrs</span><span style="color: #000000;">) 
        {
            </span><span style="color: #800080;">$lsvrname </span><span style="color: #ff0000;">= </span><span style="color: #800080;">$lsvr</span><span style="color: #000000;">.Name </span><span style="color: #ff0000;">-replace </span><span style="color: #800000;">'</span><span style="color: #800000;">\s|,|\\|:|<|>|\(|\)|\[|\]|/</span><span style="color: #800000;">'
</span><span style="color: #800080;">            $filename </span><span style="color: #ff0000;">= </span><span style="color: #800000;">"</span><span style="color: #800000;">$outputpath\$lsvrname.sql</span><span style="color: #800000;">"
</span><span style="color: #5f9ea0; font-weight: bold;">            Write-Host </span><span style="color: #800080;">$filename 
</span><span style="color: #800080;">            $lsvr</span><span style="color: #000000;">.Script() | </span><span style="color: #5f9ea0; font-weight: bold;">Out-File </span><span style="color: #800080;">$filename</span><span style="color: #000000;"> 
        } 
}</span>