Import/Export SQL Server 2000 Enterprise Manager Registered Servers

,

Powershell script to import or export SQL Server 2000 Enterprise Manager group and server registrations.

Note: SQL Server 2000 Enterprise Manager should be installed and then launched to initialize the registry keys. Close Enterprise Manager prior to running script.

To use you'll need to source the Powershell Script.

Save the file as sqlem.ps1

Launch Powershell cd to the file location

. ./sqlem.ps1

This will source the Powershell script which simply defines the functions but does not execute the script. Example usage to export and import server registrations to c:\serves.txt file:

Export-RegisteredServers c:\servers.txt

or

Import-RegisteredServers c:\servers.txt

#Exports and Imports SQL Server 2000 Entperise Manager Server Registrations
#Author: Chad Miller cmille19@tampabay.rr.com
#Created: February 15 2008

$global:appl = New-Object -comobject "SQLDMO.Application"

#######################
function Export-RegisteredServers
{
param($file)

#Dumps current SQL Server Enterprise Manager Groups and servers to specific
$appl.ServerGroups | %{$group = $_.Name; $_.RegisteredServers | % {$group + " " + $_.Name}} >> $file

}


#######################
function Add-ServerGroup
{
param($groupname)


$ServerGroup = New-Object -comobject "SQLDMO.ServerGroup"
$ServerGroup.Name = $groupname
trap {$script:Exception = $_; continue} $appl.ServerGroups.Add($ServerGroup)
$ServerGroup = $appl.ServerGroups.Item($groupname)

return $ServerGroup

}

#######################
function Add-RegisteredServer
{
param($ServerGroup, $servername)

$RegisteredServer = New-Object -comobject "SQLDMO.RegisteredServer"
$RegisteredServer.Name = $servername
$RegisteredServer.UseTrustedConnection = 1
$RegisteredServer.PersistFlags = 1
trap {$script:Exception = $_; continue} $ServerGroup.RegisteredServers.Add($RegisteredServer)

}


#######################
function Import-RegisteredServers
{
param($file)


#Registers each group/server in enterprise manager based on file generated from write-registeredServers function

Get-Content $file |foreach {$groupname, $servername = $_.split();
	$ServerGroup = Add-ServerGroup $groupname;
	Add-RegisteredServer $ServerGroup $servername}

}

Rate

5 (1)

Share

Share

Rate

5 (1)