New Laptop - How 2 Copy Registerations?

Question

New Laptop - How 2 Copy Registerations?

gfahrlander

SSCommitted

Points: 1888
More actions
April 4, 2006 at 11:20 am

#152884

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

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

gfahrlander SSCommitted Points: 1888 More actions · Answer 2

I'm not a C++ or C# programmer. Guess I'm stuck with registering every server\instance.

Thanks

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

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