Need a way to script out LinkedServers on a SQL 2005\2008 boxes but place the script in seperate files.

Question

Post reply

Need a way to script out LinkedServers on a SQL 2005\2008 boxes but place the script in seperate files.

Michael Robey

SSC Veteran

Points: 285
More actions
January 27, 2012 at 8:55 am

#254427

If I run this it scripts them out all into one file. Can this be adjusted to put them in to individual files or is there a better way to do this?
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[system.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.sqlserver.smo’) |out-null
CLS
CD SQLSERVER:\SQL\OM37345\DEFAULT\LinkedServers
GCI | %{$_.script()} | out-file "C:\LinkedServers.sql"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Will this work for you?

http://sev17.com/2010/07/scripting-linked-servers/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

This worked for me:

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue

Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Smo’) | Out-Null

foreach($linkedServer in (Get-ChildItem SQLSERVER:\SQL\MYSERVER\DEFAULT\LinkedServers)) {

$linkedServer.Script() | Out-File ("C:\LinkedServer." + $linkedServer.Name.Replace(".","_").Replace("\","$") + ".sql")

}

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Michael Robey SSC Veteran Points: 285 More actions · Answer 3

That will work perfectly. Thanks for the help as it is muchly appreciated.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Anytime, happy to help 😀

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Michael Robey SSC Veteran Points: 285 More actions · Answer 5

I created this script back in 2012 and it works fine running it under Windows XP, but now when I try to run it under my new machine Windows 7 it wont run. Could you tell me why this would be?

<# SYNOPSIS

Generates create scripts for all LinkedServers.

DESCRIPTION

PowerShell script using SMO to script out all LinkedServers.

NOTES

Requires: PowerShell Version 2.0, SMO assembly

#>

#Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue

#Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue

# Configuration data

#[string] $server = "SERVERNAME"; # SQL Server Name

#[string] $serverinstance = "SERVERNAME\DEV";# SQL Server Instance Database with the tables to script out.

#[string] $database = "DBNAME"; # Database with the tables to script out.

#[string] $folder = "S:\DBA"; # Path to export to

param ($server, $serverinstance, $database, $folder)

# Reference to SMO

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null

Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": *** LinkedServers Started ***");

foreach($linkedServer in (Get-ChildItem SQLSERVER:\SQL\$Serverinstance\LinkedServers))

{

$linkedServer.Script() | Out-File ($folder + "\" + $server + "\_ServerObjects\" + $linkedServer.Name.Replace(".","_").Replace("\","$") + ".sql")

}

Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": !!! LinkedServers Finished !!!");

Write-Output " "

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

It could be one of a lot of different reasons. What is the exact error message or behavior you are seeing running on Windows 7?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Works on Windows 8 with SQL 2012 or 2008 R2:

<# SYNOPSIS
Generates create scripts for all LinkedServers.
DESCRIPTION
PowerShell script using SQL Provider to script out all LinkedServers.
NOTES
Requires: PowerShell Version 2.0, SMO assembly
#>
param ($server, $serverinstance, $database, $folder)
# Import SQL 2012 module:
Import-Module sqlps
#if you're still using SQL 2008/R2, comment the above call to Import-Module and load the 2008 provider instead:
#Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
# Configuration data
#[string] $server = "SERVERNAME"; # SQL Server Name
#[string] $serverinstance = "SERVERNAME\DEV"; # SQL Server Instance Database with the tables to script out.
#[string] $database = "DBNAME"; # Database with the tables to script out.
#[string] $folder = "S:\DBA"; # Path to export to
Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": *** LinkedServers Started ***");
$folder=(Join-Path (Join-Path (Join-Path $folder $server) $serverinstance) "_ServerObjects")
if(!(Test-Path $folder)) {New-Item -ItemType directory -Path $folder}
foreach($linkedServer in (Get-ChildItem SQLSERVER:\SQL\$server\$Serverinstance\LinkedServers))
{
$linkedServer.Script() | Out-File (Join-Path $folder ($linkedServer.Name.Replace(".","_").Replace("\","$") + ".sql"))
}
Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": !!! LinkedServers Finished !!!");
Write-Output " "

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato