Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need a way to script out LinkedServers on a SQL 2005\2008 boxes but place the script in seperate files. Expand / Collapse
Author
Message
Posted Friday, January 27, 2012 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:42 PM
Points: 6, Visits: 117
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"



Post #1242936
Posted Friday, January 27, 2012 10:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 17,600, Visits: 15,462
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1243035
Posted Friday, January 27, 2012 11:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1243070
Posted Friday, January 27, 2012 2:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:42 PM
Points: 6, Visits: 117
That will work perfectly. Thanks for the help as it is muchly appreciated.


Post #1243143
Posted Friday, January 27, 2012 3:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:27 PM
Points: 7,107, Visits: 12,657
Anytime, happy to help

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1243170
Posted Thursday, January 16, 2014 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:42 PM
Points: 6, Visits: 117
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 " "










Post #1531747
Posted Friday, January 17, 2014 1:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1531951
Posted Friday, January 17, 2014 3:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1531982
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse