April 4, 2006 at 11:20 am
Finally getting a new Laptop!! Is there an easy way to copy all my MSSQL registrations from my old PC to new laptop?
I don't want to register 89 instances on 43 servers all over again!!
Thanks
April 4, 2006 at 12:39 pm
If you install the SQL 2005 client tools (which support your 7.0 and 2000 servers), you can import your existing registrations in Management Studio and then export them to an XML file. This file can be imported on any other computer (in Management Studio) to recreate the registrations.
If you're stuck in the SQL 2000 world, you could try using SQL-DMO to create your own import/export function. Look up the RegisteredServers collection and how to use it at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_c_r_2qpa.asp
April 5, 2006 at 9:18 am
I'm not a C++ or C# programmer. Guess I'm stuck with registering every server\instance.
Thanks
April 5, 2006 at 10:29 am
You could use Visual Basic, or even VBScript. You can even use sp_OAxxx procedures to access SQLDMO objects in T-SQL.
This script may be of some use. It won't export existing registrations, but you can create them by editing this script instead of clicking on a lot of dialog boxes. It is a variation of a script I got somewhere, possibly even a posting on this site. You can edit it to fit your needs with minimal or non-existent programming skills. It can create groups, register servers with either Windows or SQL authentication, and define aliases for servers that you access by TCP/IP address. It is VBScript, so you don't need a compiler to run it. Just edit this in Notepad, save it to a file with an extension of .vbs, and run it. It may only run if it is saved in a local file on your computer, security settings will very likely block it (with no error message) if you try to run it from a network share.
With a little programming skill and imagination, you could figure out how to loop through the ServerGroups collection and each of their RegisteredServer collections to print out the necessary "CreateGroup" and "RegisterServer" subroutine calls. Then paste that text into this script to finish the job.
---------------------------------------------------------------------------------------------------------------------------------
Option Explicit
' Declare variables
Dim dmoApp, dmoServerGroup
On Error Resume Next
' Create a reference to a DMO application
Set dmoApp = WScript.CreateObject("SQLDMO.Application")
' Check the aliases for PROD1 and PROD2 using TCP/IP connections
CreateAlias "PROD1", "DBMSSOCN,192.168.1.7"
CreateAlias "PROD2", "DBMSSOCN,192.168.1.8"
' Create Production group
CreateGroup dmoApp, "Production", dmoServerGroup
RegisterServer dmoServerGroup, "PROD1", "user", "password"
RegisterServer dmoServerGroup, "PROD2", "user", "password"
' Create Servers group
CreateGroup dmoApp, "Servers", dmoServerGroup
RegisterServer dmoServerGroup, "server1", "", ""
RegisterServer dmoServerGroup, "server2", "", ""
RegisterServer dmoServerGroup, "server3", "", ""
RegisterServer dmoServerGroup, "server4", "", ""
' Create Workstations group
CreateGroup dmoApp, "Workstations", dmoServerGroup
RegisterServer dmoServerGroup, "(LOCAL)", "", ""
RegisterServer dmoServerGroup, "wkstn1", "", ""
RegisterServer dmoServerGroup, "wkstn2", "", ""
Set dmoServerGroup = Nothing
Set dmoApp = Nothing
Private Sub CreateGroup(ByRef dmoApp, ByRef sGroup, ByRef dmoServerGroup)
On Error Resume Next
Set dmoServerGroup = dmoApp.ServerGroups(sGroup)
If Err.Number Then
Set dmoServerGroup = WScript.CreateObject("SQLDMO.ServerGroup")
dmoServerGroup.Name = sGroup
dmoApp.ServerGroups.Add dmoServerGroup
End If
End Sub
Private Sub CreateAlias(ByRef sAlias, ByRef sDefinition)
Dim sh
Set Sh = Wscript.CreateObject("WScript.Shell")
sh.RegWrite "HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo\" & sAlias, sDefinition, "REG_SZ"
Set Sh = Nothing
End Sub
Private Sub RegisterServer(ByRef dmoServerGroup, ByRef sServer, ByVal sLogin, ByVal sPassword)
Dim dmoRegServer
On Error Resume Next
Set dmoRegServer = dmoServerGroup.RegisteredServers(sServer)
If Err.Number Then
Set dmoRegServer = WScript.CreateObject("SQLDMO.RegisteredServer")
With dmoRegServer
.Name = sServer
If sLogin = "" Then
.UseTrustedConnection = 1
Else
.UseTrustedConnection = 0
.Login = sLogin
.Password = sPassword
End If
End With
dmoServerGroup.RegisteredServers.Add dmoRegServer
End If
Set dmoRegServer = Nothing
End Sub
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply