Enterprise Manager Configuration Import/Export

'
' Application: SQLEM Duper
'
' Purpose    : Copy SQL Enterprise Manager configuration from one workstation to another, including
'              server groups nested up to 5 levels.
'
' Author     : Ron Klimaszewski (ron_klimaszewski^^@^^yahoo.com)
'
' Revisions  : 2005-09-13: Initial release
'
Option Explicit
Dim ofso, osqlapp, osg
Dim gLastGroupCreated
Dim osgnew1, osgnew2, osgnew3, osgnew4, osgnew5
Dim gblnGet, gblnPut, gblnErase, gstrFileName, ocfgfile 
Dim gblnGUI, gstrTitle, gsngVersion
gsngVersion = 1.10

Set ofso = CreateObject("Scripting.FilesystemObject")
Set osqlapp = CreateObject("SQLDMO.Application")
Set osg = osqlapp.ServerGroups

Const ForReading = 1
Const ForWriting = 2


' Check if running in a GUI window
If InStr(1,wscript.FullName,"wscript.exe",1) Then 
gblnGUI=True
Else
gblnGUI=False
End If

gstrTitle = vbCrLf _
& "SQL Enterprise Manager Configuration Duper v" & gsngVersion & vbCrLf _
& String(70,"=") & vbCrLf _
& "  (c) Ron Klimaszewski - 2005/09/01       " & vbCrLf _
& String(70,"-") & vbCrLf 


Sub Main 

call GetArguments

If gblnErase Then 
call EraseSQLEMConfig
End If

If gblnGet Then 
call GetSQLEMConfig
End If 

If gblnPut Then 
call PutSQLEMConfig
End If 

On Error Resume Next 
ocfgfile.close
On Error Goto 0

WScript.Echo "Finished"

End Sub ' Main

Sub GetArguments
gblnGet = False : gblnPut = False : gblnErase = False 


'WScript.Echo gstrTitle

If gblnGUI = True Then InvalidOptions("Please use CSCRIPT to run this script")

If WScript.Arguments.Count = 0 Then InvalidOptions("ERROR:  Must specify /Get or /Put or /Erase")

If WScript.Arguments.Named.Exists("Get") Then gblnGet = True
If WScript.Arguments.Named.Exists("Put") Then gblnPut = True
If WScript.Arguments.Named.Exists("Erase") Then gblnErase = True
If WScript.Arguments.Named.Exists("Mirror") Then
gblnErase = True
gblnPut = True
End If 

If Not (gblnGet Xor gblnPut) And Not gblnErase Then 
InvalidOptions("You must select either /Get OR /Put OR /Erase")
End If

If WScript.Arguments.Named.Exists("File") Then
gstrFileName = WScript.Arguments.Named("File")

If Not ofso.FileExists(gstrFileName) And gblnPut Then
InvalidOptions("File '" & gstrFileName & "' not found")
End If 

If gblnPut = True Then 
' Open the text file
On Error Resume Next 
Set ocfgfile = ofso.OpenTextFile(gstrFileName, ForReading)
If Err.number > 0 Then
InvalidOptions("ERROR OPENING FILE '" & gstrFileName & "': " & Err.number & " (" & Err.description & ")")
End If
On Error Goto 0
End If 

If gblnGet = True Then
' If the file exists, then don't overwrite it!
' Writing the configuration to a text file, so open it up here
On Error Resume Next 
Set ocfgfile = ofso.CreateTextFile(gstrFileName, False)
If Err.number > 0 Then
InvalidOptions("ERROR CREATING FILE '" & gstrFileName & "': " & Err.number & " (" & Err.description & ")")
Else
WScript.Echo "Writing configuration file to " & gstrFileName
End If
On Error Goto 0
End If 

ElseIf gblnPut = True Then
 InvalidOptions("Must specify /File when using the /Put option")
End If 


End Sub ' GetArguments

Sub InvalidOptions(lstrErrorMessage)
Dim lstrUsage

lstrUsage = gstrTitle & vbCrLf _
& "This utility reads from or writes to the SQL Enterprise Manager" & vbCrLf _
& "configuration.  It can handle Server Groups up to 5 levels deep" & vbCrLf _
& "" & vbCrLf _
& "  USAGE:" & vbCrLf _
& "      cscript " & WScript.ScriptName & " [/Get | /Put] /File:<filename>" & vbCrLf _
& "" & vbCrLf _
& "     /Get    - Retrieve the information from the local SQL EM" & vbCrLf _
& "     /Put    - Write the information to the local SQL EM" & vbCrLf _
& "     /File   - /Get will write the config to a text file  (optional)" & vbCrLf _
& "             - /Put will read the config from a text file (required)" & vbCrLf _
& "     /Erase  - Erase the current SQL EM configuration (CAREFUL!)" & vbCrLf _
& "     /Mirror - Combines /Put and /Erase (/File is required)" & vbCrLf _
& "" & vbCrLf _
& "ERROR MESSAGE: " & vbCrLf _
& "   " & lstrErrorMessage & vbCrLf

If gblnGUI Then
wscript.Echo lstrUSAGE
Else
wscript.stderr.WriteLine lstrUSAGE
End If

wscript.quit(1)

End Sub ' InvalidOptions

Sub WriteOutput(byval lstrString)


If IsObject(ocfgfile) Then

On Error Resume Next
ocfgfile.WriteLine(lstrString)
If Err.Number <> 0 Then 
WScript.Echo "Error writing string: " & lstrString
End If
Err.Clear
On Error Goto 0 
Else
WScript.Echo lstrString
End If

End Sub ' WriteOutput


'
' Read SQL Enterprise Manager configuration
'
Sub GetSQLEMConfig
Dim grp1, grp2, grp3, grp4, grp5
Dim server, strTmp
For Each grp1 In osg

' Level 1 Registered Servers
If grp1.ServerGroups.Count = 0 And grp1.RegisteredServers.Count = 0 Then 
WriteOutput("\" & grp1.Name)
End If 

For Each server In grp1.RegisteredServers
WriteOutput("\" & grp1.Name & "," & GetSQLRegisterInfo(server))
Next ' Level 1 servers

' Level 2 Groups
 For Each grp2 In grp1.ServerGroups
 If grp2.ServerGroups.Count = 0 And grp2.RegisteredServers.Count = 0 Then 
 WriteOutput("\" & grp1.Name & "\" & grp2.Name)
 End If 

  ' Level 2 Servers
For Each server In grp2.RegisteredServers
WriteOutput("\" & grp1.Name &"\" & grp2.Name & "," & GetSQLRegisterInfo(server))
Next ' Level 2 Servers

' Level 3 Groups
 For Each grp3 In grp2.ServerGroups
 If grp3.ServerGroups.Count = 0 And grp3.RegisteredServers.Count = 0 Then 
 WriteOutput("\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name)
 End If 

  ' Level 3 Servers
For Each server In grp3.RegisteredServers
WriteOutput("\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "," & GetSQLRegisterInfo(server))
Next ' Level 3 Servers

' Level 4 Groups
 For Each grp4 In grp3.ServerGroups
 If grp4.ServerGroups.Count = 0 And grp4.RegisteredServers.Count = 0 Then 
 WriteOutput("\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "\" & grp4.Name )
 End If 

  ' Level 4 Servers
For Each server In grp4.RegisteredServers
WriteOutput("\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "\" & grp4.Name & "," & GetSQLRegisterInfo(server))
Next ' Level 4 Servers

' Level 5 Groups
 For Each grp5 In grp4.ServerGroups
 If grp5.ServerGroups.Count = 0 And grp5.RegisteredServers.Count = 0 Then 
 WriteOutput("\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "\" & grp4.Name & "\" & grp5.Name )
 End If 

  ' Level 5 Servers
For Each server In grp5.RegisteredServers
WriteOutput("\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "\" & grp4.Name & "\" & grp5.Name & "," & GetSQLRegisterInfo(server))
Next ' Level 5 Servers
Next ' Level 5 Groups
Next ' Level 4 Groups
Next ' Level 3 Groups
  Next ' Level 2 Groups
Next ' Level 1 Groups

End Sub ' GetSQLEMConfig


'
' Retrieve information about a registered server
'
Function GetSQLRegisterInfo(ByRef oServer)
  GetSQLRegisterInfo =  oserver.Name _
  & "," & oserver.Login _ 
  & "^" & oserver.Password _
  & "^" & oserver.PersistFlags _
  & "^" & CStr(oserver.UseTrustedConnection)
End Function

'
' Write out the Enterprise Manager groups and registered servers
'
Sub PutSQLEMConfig
Dim lstrGroupString, ocfgfile, strLine 
Dim lobjGroupTmp
On Error Resume Next 
Set ocfgfile = ofso.OpenTextFile(gstrFileName)
If Err.number > 0 Then
WScript.Echo "ERROR OPENING FILE '" & gstrFileName & "': " & Err.number & " (" & Err.description & ")"
WScript.Quit(1)
End If
On Error Goto 0
Do While ocfgfile.AtEndOfStream <> True
strLine = Trim(ocfgfile.ReadLine)
If Len(strLine) = 0 Or Mid(strLine,1,1) = "#" Then 
' Skip any lines that are blank or are commented out
Else 

lstrGroupString = Split(strLine,",")(0)

If UBound(Split(strLine,",")) = 0 Then
' only creating groups
If lstrGroupString <> gLastGroupCreated Then ' (this is a new one)
Set lobjGroupTmp = PutServerGroup(Split(strLine,",")(0))
End If 
Else
' create groups and register instance
If lstrGroupString <> gLastGroupCreated Then ' (this is a new one)
Set lobjGroupTmp = PutServerGroup(Split(strLine,",")(0))
End If 
PutRegisteredServer lobjGroupTmp, Split(strLine,",")(1), Split(strLine,",")(2)
End If 
End If ' Check for invalid/comment lines
Loop


End Sub 'PutSQLEMConfig

Function PutServerGroup(byval lstrGroupName)
'Dim osgnew1, osgnew2, osgnew3, osgnew4, osgnew5
Dim odiclvl1, odiclvl2, odiclvl3, odiclvl4, odiclvl5
Dim lintLevels, lstrTmpGroupName, i

Set odiclvl1 = CreateObject("Scripting.Dictionary")
Set odiclvl2 = CreateObject("Scripting.Dictionary")
Set odiclvl3 = CreateObject("Scripting.Dictionary")
Set odiclvl4 = CreateObject("Scripting.Dictionary")
Set odiclvl5 = CreateObject("Scripting.Dictionary")

Set osgnew5 = Nothing
Set osgnew4 = Nothing
Set osgnew3 = Nothing
Set osgnew2 = Nothing
Set osgnew1 = Nothing

odiclvl1.RemoveAll : odiclvl2.RemoveAll : odiclvl3.RemoveAll 
odiclvl4.RemoveAll : odiclvl5.RemoveAll

gLastGroupCreated = lstrGroupName

WScript.Echo "Creating group: " & lstrGroupName

lintLevels = UBound(Split(lstrGroupName,"\"))

'
' First (root) level
'
lstrTmpGroupName = Split(lstrGroupName,"\")(1)

' Get a list of root-level group names
For i = 1 To osg.Count
odiclvl1.Add osg.Item(i).Name, i
Next 

If odiclvl1.Exists(lstrTmpGroupName) Then 
Set osgnew1 = osg.Item(odiclvl1(lstrTmpGroupName))
Else
Set osgnew1 = CreateObject("SQLDMO.ServerGroup")
osgnew1.Name = lstrTmpGroupName
On Error Resume Next 
osgnew1.Name = lstrTmpGroupName 
osg.Add(osgnew1)
If Err.number <> 0 Then 
WScript.Echo "  Error with L1 Group Name '" _
& lstrTmpGroupName & "':  Error #" _
& Err.number & "(" & Err.description & ")"
Err.clear
End If
On Error Goto 0
End If 

'
' Second Level
'
If lintLevels < 2 Then
Set PutServerGroup = osgnew1
Exit Function 
Else 
lstrTmpGroupName = Split(lstrGroupName,"\")(2)

' Get list of Second-Level group names under the Level 1
For i = 1 To osgnew1.ServerGroups.Count
odiclvl2.Add osgnew1.ServerGroups.Item(i).Name, i
Next 

If odiclvl2.Exists(lstrTmpGroupName) Then 
Set osgnew2 = osgnew1.ServerGroups.Item(odiclvl2(lstrTmpGroupName))
Else
Set osgnew2 = CreateObject("SQLDMO.ServerGroup")
osgnew2.Name = lstrTmpGroupName
On Error Resume Next 
osgnew1.ServerGroups.Add(osgnew2)
If Err.number <> 0 Then 
WScript.Echo "  Error with L2 Group Name '" _
& lstrTmpGroupName & "':  Error #" _
& Err.number & "(" & Err.description & ")"
Err.clear
End If
On Error Goto 0
End If 
End If ' End of Level 2


'
' Third Level
'
If lintLevels < 3 Then
Set PutServerGroup = osgnew2
Exit Function 
Else 
lstrTmpGroupName = Split(lstrGroupName,"\")(3)

' Get list of Third -Level group names under the Level 2
For i = 1 To osgnew2.ServerGroups.Count
odiclvl3.Add osgnew2.ServerGroups.Item(i).Name, i
Next 

If odiclvl3.Exists(lstrTmpGroupName) Then 
Set osgnew3 = osgnew2.ServerGroups.Item(odiclvl3(lstrTmpGroupName))
Else
Set osgnew3 = CreateObject("SQLDMO.ServerGroup")
osgnew3.Name = lstrTmpGroupName
On Error Resume Next 
osgnew2.ServerGroups.Add(osgnew3)
If Err.number <> 0 Then 
WScript.Echo "  Error with L3 Group Name '" _
& lstrTmpGroupName & "':  Error #" _
& Err.number & "(" & Err.description & ")"
Err.clear
End If
On Error Goto 0
End If 
End If ' End of Level 3


'
' Fourth Level
'
If lintLevels < 4 Then
Set PutServerGroup = osgnew3
Exit Function 
Else 
lstrTmpGroupName = Split(lstrGroupName,"\")(4)

' Get list of Fourth-Level group names under Level 3
For i = 1 To osgnew3.ServerGroups.Count
odiclvl4.Add osgnew3.ServerGroups.Item(i).Name, i
Next 

If odiclvl4.Exists(lstrTmpGroupName) Then 
Set osgnew4 = osgnew3.ServerGroups.Item(odiclvl4(lstrTmpGroupName))
Else
Set osgnew4 = CreateObject("SQLDMO.ServerGroup")
osgnew4.Name = lstrTmpGroupName
On Error Resume Next 
osgnew3.ServerGroups.Add(osgnew4)
If Err.number <> 0 Then 
WScript.Echo "  Error with L4 Group Name '" _
& lstrTmpGroupName & "':  Error #" _
& Err.number & "(" & Err.description & ")"
Err.clear
End If
On Error Goto 0
End If 
End If ' End of Level 4

'
' Fifth Level
'
If lintLevels < 5 Then
Set PutServerGroup = osgnew4
Exit Function 
Else 
lstrTmpGroupName = Split(lstrGroupName,"\")(5)

' Get list of Fifth-Level group names under Level 4
For i = 1 To osgnew4.ServerGroups.Count
odiclvl5.Add osgnew4.ServerGroups.Item(i).Name, i
Next 

If odiclvl5.Exists(lstrTmpGroupName) Then 
Set osgnew5 = osgnew4.ServerGroups.Item(odiclvl5(lstrTmpGroupName))
Else
Set osgnew5 = CreateObject("SQLDMO.ServerGroup")
osgnew5.Name = lstrTmpGroupName
On Error Resume Next 
osgnew4.ServerGroups.Add(osgnew5)
If Err.number <> 0 Then 
WScript.Echo "  Error with L5 Group Name '" _
& lstrTmpGroupName & "':  Error #" _
& Err.number & "(" & Err.description & ")"
Err.clear
End If
On Error Goto 0
End If 
End If ' End of Level 5

Set PutServerGroup = osgnew5
WScript.Echo "    5=" & osgnew5.Name


End Function

Function PutRegisteredServer(byref lobjGroup, byval lstrInstanceName, byval lstrInstanceParameters)
Dim osrvnew 
WScript.Echo " + register server " & lstrInstanceName '& " - " & lstrInstanceParameters

If Not IsObject(lobjGroup) Or UBound(Split(lstrInstanceParameters,"^")) < 3 Then
WScript.Echo "    * ERROR: Incorrect registration parameters for '" & lstrInstanceName & "' - " & lstrInstanceParameters
Else 
Set osrvnew = CreateObject("SQLDMO.RegisteredServer")
With osrvnew
.Name = lstrInstanceName
If Split(lstrInstanceParameters,"^")(0) <> "" Then 
.Login = Split(lstrInstanceParameters,"^")(0)
End If
If Split(lstrInstanceParameters,"^")(1) <> "" Then 
.Password = Split(lstrInstanceParameters,"^")(1)
End If
If Split(lstrInstanceParameters,"^")(2) <> "" Then 
.PersistFlags = Split(lstrInstanceParameters,"^")(2)
End If
If Split(lstrInstanceParameters,"^")(3) <> "" Then 
.UseTrustedConnection = Split(lstrInstanceParameters,"^")(3)
End If
End With 
On Error Resume Next 
lobjGroup.RegisteredServers.Add(osrvnew)
If Err.number <> 0 Then 
WScript.Echo "    * ERROR: " & Err.number & " (" & Err.description & ")"
End If 
'PutRegisteredServer = Err.number & "," & Err.description
On Error Goto 0
Set osrvnew = Nothing
End If ' validating objs and params

End Function

Sub RemoveObject(byref lobjToRemove, byval lstrString)
Dim lstrGroupString, lstrInstance

lstrGroupString = Split(lstrString,",")(0)
If UBound(Split(lstrString,",")) > 0 Then 
lstrInstance = Split(lstrString,",")(1)
End If 

If lstrGroupString <> gLastGroupCreated Then 
WScript.Echo "REMOVING GROUP : " & lstrGroupString
gLastGroupCreated = lstrGroupString
End If 

If lstrInstance <> "" Then 
WScript.Echo "               :  + " & lstrInstance
End If 

If IsObject(lobjToRemove) Then
On Error Resume Next 
lobjToRemove.Remove
If Err.number > 0 Then 
WScript.Echo "    * ERROR: " & Err.number & " (" & Err.description & ")"
End If
On Error Goto 0
Else
WScript.Echo "    * ERROR: The parameter passed was not an object"
End If



End Sub 'RemoveObject


'
' Erase the SQL Enterprise Manager configuration
'
Sub EraseSQLEMConfig
Dim grp1, grp2, grp3, grp4, grp5
Dim server, strTmp
For Each grp1 In osg

' Level 1 Registered Servers
For Each server In grp1.RegisteredServers
RemoveObject server, "\" & grp1.Name & "," & GetSQLRegisterInfo(server)
Next ' Level 1 servers

' Level 2 Groups
 For Each grp2 In grp1.ServerGroups

  ' Level 2 Servers
For Each server In grp2.RegisteredServers
RemoveObject server, "\" & grp1.Name &"\" & grp2.Name & "," & GetSQLRegisterInfo(server)
Next ' Level 2 Servers

' Level 3 Groups
 For Each grp3 In grp2.ServerGroups

  ' Level 3 Servers
For Each server In grp3.RegisteredServers
RemoveObject server, "\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "," & GetSQLRegisterInfo(server)
Next ' Level 3 Servers


' Level 4 Groups
 For Each grp4 In grp3.ServerGroups

  ' Level 4 Servers
For Each server In grp4.RegisteredServers
RemoveObject server, "\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "\" & grp4.Name & "," & GetSQLRegisterInfo(server)
Next ' Level 4 Servers


' Level 5 Groups
 For Each grp5 In grp4.ServerGroups
  ' Level 5 Servers
For Each server In grp5.RegisteredServers
RemoveObject server, "\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "\" & grp4.Name & "\" & grp5.Name & "," & GetSQLRegisterInfo(server)
Next ' Level 5 Servers

 If grp5.ServerGroups.Count = 0 And grp5.RegisteredServers.Count = 0 Then 
 RemoveObject grp5, "\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "\" & grp4.Name & "\" & grp5.Name 
 End If 

Next ' Level 5 Groups

 If grp4.ServerGroups.Count = 0 And grp4.RegisteredServers.Count = 0 Then 
 RemoveObject grp4, "\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name & "\" & grp4.Name 
 End If 

Next ' Level 4 Groups

 If grp3.ServerGroups.Count = 0 And grp3.RegisteredServers.Count = 0 Then 
 RemoveObject grp3, "\" & grp1.Name & "\" & grp2.Name & "\" & grp3.Name
 End If 


Next ' Level 3 Groups

 If grp2.ServerGroups.Count = 0 And grp2.RegisteredServers.Count = 0 Then 
 RemoveObject grp2, "\" & grp1.Name & "\" & grp2.Name
 End If 

  Next ' Level 2 Groups

If grp1.ServerGroups.Count = 0 And grp1.RegisteredServers.Count = 0 Then 
RemoveObject grp1, "\" & grp1.Name
End If 

Next ' Level 1 Groups

End Sub ' EraseSQLEMConfig


call Main