Enterprise Manager Configuration Import/Export
For those that do not have SQL 2005 Management Studio, this utility reads from or writes to the SQL Enterprise Manager configuration. It can handle a Server Group hierarchy up to 5 levels deep. Usage instructions are included in the script.
Run with cscript.exe.
NOTE: Server registrations using SQL (not Windows) authentication write out the password in plain text. Be sure to secure or delete the file when done.
'
' 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
