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

Rate

Share

Share

Rate