'* Author: Mark T. Boyer, Premier Application Development Support
'Copyright (C) 2002 Microsoft Corporation
'All rights reserved.
'
'THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
'EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
'MERCHANTIBILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
'
'* Purpose: This is a command line tool for saving the SQL Enterprise Manager
'* groups and registered servers. This tool has three switches to
'* control it's behavior:
'* Usage:
'* -s <filename> Saves the SQL Enterprise Manager groups and registered servers to the xml file
'* -r <filename> Restores the SQL Enterprise Manager groups and registered servers from the xml file
'*
'***********************************************************************************************
'*--------------------------------------------------------------------------
'* Switches: '-s <filename>', '-r <filename>'
'*
'* Required: ('-s <filename>') or ('-r-xml <filename>')
'*
'* Examples: 'SQLEMSET -s c:\sqlemset.xml', Saves the current SQL
'* Enterprise manager Groups and registered servers to the file.
'*
'* 'SQLEMSET -r c:\sqlemset.xml', Builds the SQL Enterprise
'* manager Groups and Registered servers from the file.
'*
'*--------------------------------------------------------------------------
'For more information on this tool contact Mark Boyer via email at mboyer@microsoft.com
'***********************************************************************************************
Sub Save(ByVal xmlFilename)
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oSQLServer
Dim xmlDoc
Dim newElement
set oSQLServer = CreateObject("SQLDMO.SQLServer")
set oApplication = CreateObject("SQLDMO.Application")
set xmlDoc = CreateObject("Microsoft.XMLDOM")
set oServerGroups = oApplication.ServerGroups
set newElement = xmlDoc.CreateElement("SQLServerGroups")
xmlDoc.AppendChild(newElement)
call RecurseSave(newElement, oServerGroups)
xmlDoc.Save(xmlFilename)
WScript.Echo "Settings Saved"
End Sub Private Sub RecurseSave(ByRef curServerGroupNode, ByRef myServerGroups)
Dim oServerGroup
Dim oRegisteredServer
Dim xmlDoc
Dim xmlNode
Dim curXMLNode
Dim newServerGroupNode
Dim xmlNodeAttr
Dim newRegisteredServer
Dim newSGElement
Dim newSGName
Dim newRSName
Dim newRSLogin
Dim newRSPass
Dim newRSTrust
Dim txtNode
set xmlDoc = curServerGroupNode.OwnerDocument
For Each oServerGroup In myServerGroups
set newServerGroupNode = xmlDoc.CreateElement("ServerGroup")
set newSGName = xmlDoc.CreateAttribute("Name")
newSGName.value = oServerGroup.Name
newServerGroupNode.setAttributeNode newSGName
curServerGroupNode.AppendChild(newServerGroupNode)
For Each oRegisteredServer In oServerGroup.RegisteredServers
set newRegisteredServer = xmlDoc.CreateElement("RegisteredServer")
set newRSName = xmlDoc.CreateAttribute("Name")
newRSName.value = oRegisteredServer.Name
newRegisteredServer.setAttributeNode newRSName
set newRSLogin = xmlDoc.CreateAttribute("Login")
newRSLogin.value = oRegisteredServer.Login
newRegisteredServer.setAttributeNode newRSLogin
set newRSPass = xmlDoc.CreateAttribute("Password")
newRSPass.value = oRegisteredServer.Password
newRegisteredServer.setAttributeNode newRSPass
set newRSTrust = xmlDoc.CreateAttribute("IsNTAuth")
newRSTrust.value = CStr(oRegisteredServer.UseTrustedConnection)
newRegisteredServer.setAttributeNode newRSTrust
newServerGroupNode.AppendChild(newRegisteredServer)
Next
If oServerGroup.ServerGroups.Count > 0 Then
call RecurseSave(newServerGroupNode, oServerGroup.ServerGroups)
End If
Next
End Sub
Sub Restore(ByVal xmlFilename)
Dim oApplication
set oApplication = CreateObject("SQLDMO.Application")
Dim xmlDoc
set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.Load(xmlFilename)
Dim xmlNode
set xmlNode = xmlDoc.DocumentElement
call RecurseRestore(xmlNode, oApplication.ServerGroups)
WScript.Echo "Settings Restored"
End Sub
Private Sub RecurseRestore(ByRef xmlDoc, ByRef theServerGroups)
Dim oServerGroup
Dim oRegisteredServer
Dim XMLNode
Dim childXMLNode
Dim XMLElement
For Each XMLNode In xmlDoc.childnodes
If XMLNode.nodeName = "ServerGroup" Then
' Check to see if this servergroup is already out there...
Dim ServerGroupCount
Dim a
a = 0 'Used to indicate we found a ServerGroup 0=no; 1=yes
For ServerGroupCount = 1 To theServerGroups.Count
If theServerGroups.Item(ServerGroupCount).Name = XMLNode.getAttribute("Name") Then
'Found the ServerGroup, don't add this one.
a = 1
Exit For
End If
Next
If a = 1 Then
' Found the ServerGroup... assign it...
set oServerGroup = theServerGroups.Item(ServerGroupCount)
Else
' No ServerGroup found, make one...
Dim newServerGroup
set newServerGroup = CreateObject("SQLDMO.ServerGroup")
newServerGroup.Name = XMLNode.getAttribute("Name")
theServerGroups.Add(newServerGroup)
set oServerGroup = newServerGroup
End If
For Each childXMLNode In XMLNode.ChildNodes
If childXMLNode.nodeName = "RegisteredServer" Then
'Check to see if this server is already registered..
If oServerGroup.RegisteredServers.Count > 0 Then
For Each oRegisteredServer In oServerGroup.RegisteredServers
If oRegisteredServer.Name = childXMLNode.getAttribute("Name") Then
' Now remove it so we can add it back later...
oRegisteredServer.Remove()
End If
Next
End If
Dim newRegisteredServer
set newRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
' Now register the server...
If childXMLNode.getAttribute("Login") = "" Then
newRegisteredServer.Login = " "
Else
newRegisteredServer.Login = childXMLNode.getAttribute("Login")
End If
newRegisteredServer.Name = childXMLNode.getAttribute("Name")
newRegisteredServer.Password = childXMLNode.getAttribute("Password")
newRegisteredServer.UseTrustedConnection = CLng(childXMLNode.getAttribute("IsNTAuth"))
oServerGroup.RegisteredServers.Add(newRegisteredServer)
End If
Next
call RecurseRestore(XMLNode, oServerGroup.ServerGroups)
End If
Next
End Sub
Dim xmlFileName
Dim Args
Dim x
Args = wscript.arguments.count
If Wscript.arguments.Count > 0 Then
Set objArgs = Wscript.Arguments
For x=0 to Args-1
If UCase(objArgs(x)) = "-S" Then
'Save Settings...
If x+1 >= Args Then
Wscript.echo "Please specify a filename: '-s <filename>'"
Else
xmlFileName = objArgs(x+1)
If xmlFileName = "" Then
Wscript.echo "Please specify a filename: '-s <filename>'"
Else
Save(xmlFileName)
End If
End If
ElseIf UCase(objArgs(x)) = "-R" Then
'Restore Settings...
If x+1 >= Args Then
Wscript.echo "Please specify a filename: '-s <filename>'"
Else
xmlFileName = objArgs(x+1)
If xmlFileName = "" Then
Wscript.echo "Please specify a filename: '-r <filename>'"
Else
Restore(xmlFileName)
End If
End If
End If
Next
End If