I have a development laptop I frequently need to reimage (ghost). I have about 25 SQL Servers registered in Enterprise Manager. Some use Windows Auth, some SQL Auth. Can I export the registration information so I don’t have to re-enter it every time
I rebuild my laptop?
I have been able to export/import the server aliases using this key (this works fine): [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client\ConnectTo]
I have also exported/imported: [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X]
But All I get are the groups, no servers.
TIA
It is possible. You have to use DMO to save the registered servers to a
file, from the source machine. And then move this file to the target machine
and add the servers on the target machine by looping through the rows in the
file (again you will have to use DMO).
I hope this gives you a starting point. If not, post back and I'll try to
post some code.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
<hpux9@.nospam.nospam> wrote in message
news:2DFF37FF-E4D3-4FFC-BF8E-5DFA8DA9C482@.microsoft.com...
> I have a development laptop I frequently need to reimage (ghost). I have
about 25 SQL Servers registered in Enterprise Manager. Some use Windows
Auth, some SQL Auth. Can I export the registration information so I don't
have to re-enter it every time I rebuild my laptop?
> I have been able to export/import the server aliases using this key (this
works fine):
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Client\ConnectTo]
>
> I have also exported/imported:
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X]
> But All I get are the groups, no servers.
> TIA
>
|||Hi TIA,
From your descriptions, I understood that you would like to export all
registered Server and then import them in a new machine. Have I understood
you? If there is anything I misunderstood, please feel free to let me know
Based on my scope, The method by exporting Register Files is not supported
from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
could do it by creating SQL-DMO
Here are the steps:
1. Create a vbs file and call it readreg.vbs which contains the following.
Please substitute [servername] with SQL Server name and [password] with the
SA password.
It also creates a table RegServers in pubs database so make sure that this
does not exist. This script collects information about the currently
registered servers and stores the information in the RegServers table.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername", "sa", "password"
oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
varchar(30),
SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
For Each oServerGroup in oApplication.ServerGroups
For Each oRegisteredServer in oServerGroup.RegisteredServers
MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"
oSQLServer.ExecuteImmediate MsgOutput
Next
Next
oSQLServer.Disconnect
msgbox "complete"
2. To run, open a command-prompt and run "cscript readreg.vbs".
3. To verify that the information was retrieved correctly, go to the SQL
Server
specified in the script and run the following:
use pubs
select * from RegServers
4. Create another vbs file and call it createreg.vbs which contains the
following:
Again substitute [servername] with SQL Server name and [password] with the
sa
password.
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim oQueryResults
Dim MsgOutput
Dim num
Dim SrvNameOutput
Dim LoginOutput
Dim PassOutput
Dim AuthOutput
Dim GetLoginOutput
set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups
oSQLServer.Connect "servername" , "sa", "password"
set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
SLogin,
SPassword, IsNtAuth from pubs..RegServers")
For num = 1 To oQueryResults.Rows
SrvNameOutput = oQueryResults.GetColumnString(num, 1)
LoginOutput = oQueryResults.GetColumnString(num, 2)
PassOutput = oQueryResults.GetColumnString(num, 3)
AuthOutput = oQueryResults.GetColumnString(num, 4)
If LoginOutput = "" Then
LoginOutput = " "
End if
Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
oNewRegisteredServer.Login = LoginOutput
oNewRegisteredServer.Name = SrvNameOutput
oNewRegisteredServer.Password = PassOutput
oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
oServerGroups("SQL Server
Group").RegisteredServers.Add(oNewRegisteredServer )
Next
Msgbox "complete"
5. To run, open a command-prompt and run "cscript createreg.vbs".
NOTE:
a. When doing the tests, please make sure that Enterprise Manager is
closed.
b. The script gathers information about the registered servers for the
currently
logged on user.
c. If you want to test on a single machine, you may do the following:
- run readreg.vbs
- Delete the registration from Enterprise Manager or delete the entries in
the
following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered
Servers X
Please test the method and I strongly recommand you doing this in a
development machine first
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for the scripts. I have changed them to JavaScript and am using XML to do the export/import. Is it possible to get the Server aliases using SQLDMO, or should I just read straight from the registry?
Thanks
-Kevin
""Mingqing Cheng [MSFT]"" wrote:
> Hi TIA,
> From your descriptions, I understood that you would like to export all
> registered Server and then import them in a new machine. Have I understood
> you? If there is anything I misunderstood, please feel free to let me know
> Based on my scope, The method by exporting Register Files is not supported
> from SQL7 SP3 as encryption has been put in the process. Furtuantely, we
> could do it by creating SQL-DMO
> Here are the steps:
> 1. Create a vbs file and call it readreg.vbs which contains the following.
> Please substitute [servername] with SQL Server name and [password] with the
> SA password.
> It also creates a table RegServers in pubs database so make sure that this
> does not exist. This script collects information about the currently
> registered servers and stores the information in the RegServers table.
> Dim oApplication
> Dim oServerGroups
> Dim oServerGroup
> Dim oRegisteredServer
> Dim oNewRegisteredServer
> Dim oSQLServer
> Dim MsgOutput
> set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oApplication = CreateObject("SQLDMO.Application")
> Set oServerGroups = oApplication.ServerGroups
> oSQLServer.Connect "servername", "sa", "password"
> oSQLServer.ExecuteImmediate "Create table pubs..RegServers(ServerName
> varchar(30),
> SLogin varchar(30), SPassword varchar(30), IsNtAuth varchar(2))"
> For Each oServerGroup in oApplication.ServerGroups
> For Each oRegisteredServer in oServerGroup.RegisteredServers
> MsgOutput = "Insert Into Pubs..RegServers values("
> MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
> oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
> CStr(oRegisteredServer.UseTrustedConnection) + "')"
> oSQLServer.ExecuteImmediate MsgOutput
> Next
> Next
> oSQLServer.Disconnect
> msgbox "complete"
>
> 2. To run, open a command-prompt and run "cscript readreg.vbs".
> 3. To verify that the information was retrieved correctly, go to the SQL
> Server
> specified in the script and run the following:
> use pubs
> select * from RegServers
> 4. Create another vbs file and call it createreg.vbs which contains the
> following:
> Again substitute [servername] with SQL Server name and [password] with the
> sa
> password.
> Dim oApplication
> Dim oServerGroups
> Dim oServerGroup
> Dim oRegisteredServer
> Dim oNewRegisteredServer
> Dim oSQLServer
> Dim oQueryResults
> Dim MsgOutput
> Dim num
> Dim SrvNameOutput
> Dim LoginOutput
> Dim PassOutput
> Dim AuthOutput
> Dim GetLoginOutput
> set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oApplication = CreateObject("SQLDMO.Application")
> Set oServerGroups = oApplication.ServerGroups
> oSQLServer.Connect "servername" , "sa", "password"
> set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
> SLogin,
> SPassword, IsNtAuth from pubs..RegServers")
> For num = 1 To oQueryResults.Rows
> SrvNameOutput = oQueryResults.GetColumnString(num, 1)
> LoginOutput = oQueryResults.GetColumnString(num, 2)
> PassOutput = oQueryResults.GetColumnString(num, 3)
> AuthOutput = oQueryResults.GetColumnString(num, 4)
> If LoginOutput = "" Then
> LoginOutput = " "
> End if
> Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
> oNewRegisteredServer.Login = LoginOutput
> oNewRegisteredServer.Name = SrvNameOutput
> oNewRegisteredServer.Password = PassOutput
> oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
> oServerGroups("SQL Server
> Group").RegisteredServers.Add(oNewRegisteredServer )
> Next
> Msgbox "complete"
> 5. To run, open a command-prompt and run "cscript createreg.vbs".
> NOTE:
> a. When doing the tests, please make sure that Enterprise Manager is
> closed.
> b. The script gathers information about the registered servers for the
> currently
> logged on user.
> c. If you want to test on a single machine, you may do the following:
> - run readreg.vbs
> - Delete the registration from Enterprise Manager or delete the entries in
> the
> following registry key:
> HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
> Server\80\Tools\SQLEW\Registered
> Servers X
> Please test the method and I strongly recommand you doing this in a
> development machine first
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
|||Hi Kevin,
Does the script works fine with you? Based on my scope, I am afraid you
could not find Server Aliases by means of SQLDMO. I think reading directly
from Registry is fine.
BTW, It is highly appreciated if you could share us with your JavaScript
and XML files among Newsgroup Community. In this way, I believe, provided
multi ways for those who may encounter the same issue. Thanks in advance.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for your help! The scripts below are a hack, but they work fine for me.
These scripts can be used to dump/load SQL registration information so you don’t have to enter/re-enter it by hand (quite handy after you re-image your development workstation).
Dump_SQL_Reg.js
===============
Run from the command line to dump all SQL registration for the current machine and user to an XML file.
cscript /nologo Dump_SQL_Reg.js > servers.xml
NOTE: If there are any SQL servers using SQL auth, the passwords will be in clear text in the XML file.
Load_SQL_Reg.js
===============
Run from the command line to load SQL registrations from servers.xml (in same dir) for the currently logged in user.
cscript /nologo Load_SQL_Reg.js > load.log
Yes you can edit the servers.xml by hand and just use the loader script.
I whipped these up in a hurry, so I would not be surprised if there are some things it can’t deal with.
If you have any questions, let me know.
Sample of the XML file
======================
<?xml version='1.0' standalone='yes' ?>
<registrations>
<aliaslist>
<alias name='App1PRD_DB' data='DBMSSOCN,PRDDB01,1433'/>
<alias name='App2PRD_DB' data='DBMSSOCN, ,PRDDB01,1433'/>
<alias name='App1DEV_DB' data='DBMSSOCN,DEVDB01,1433'/>
<alias name='App2DEV_DB' data='DBMSSOCN, ,DEVDB01,1433'/>
</aliaslist>
<group name='App1'>
<reg server='App1_PRD_DB' login='' password='' trustedyn='1'/>
<reg server='App1_DEV_DB' login='' password='' trustedyn='1'/>
</group>
<group name='App2'>
<reg server=App2PRD_DB' login='XXXX' password='XXXX' trustedyn='0'/>
<reg server='App2DEV_DB' login='XXXX' password='XXXX' trustedyn='0'/>
</group>
</registrations>
//*************************************************//
// Dump_SQL_Reg.js
var oApplication = new ActiveXObject ("SQLDMO.Application");
var oServerGroups = oApplication.ServerGroups
var reReplaceXMLChars = new RegExp ("<|>|&|'","g");
var serverGroupEnum = new Enumerator(oServerGroups);
var xml = "<?xml version='1.0' standalone='yes' ?>\n<registrations>\n";
// Grab the alias list (if any)
var aliaslist = getSQLAliasXML();
xml += aliaslist;
for (;!serverGroupEnum.atEnd();serverGroupEnum.moveNex t())
{
oServerGroup = serverGroupEnum.item();
xml += " <group name='" + oServerGroup.Name.replace(reReplaceXMLChars, XMLEncode) +"'>\n";
registeredServerEnum = new Enumerator(oServerGroup.RegisteredServers);
for (;!registeredServerEnum.atEnd();registeredServerEn um.moveNext())
{
oRegisteredServer = registeredServerEnum.item();
xml += " <reg server='" + oRegisteredServer.Name.replace(reReplaceXMLChars, XMLEncode) + "' ";
xml += "login='" + oRegisteredServer.Login.replace(reReplaceXMLChars, XMLEncode) + "' ";
xml += "password='" + oRegisteredServer.Password.replace(reReplaceXMLCha rs, XMLEncode) + "' ";
xml += "trustedyn='" + oRegisteredServer.UseTrustedConnection + "'/>\n";
}
xml +=" </group>\n";
}
xml += "</registrations>";
WScript.Echo (xml);
/*
|| returns the alias on the current machine in an XML fragment
|| <aliaslist>
|| <alias name="my server alias" data="DBMSSOCN,xxxxxx,1433"/>
|| </aliaslist>
*/
function getSQLAliasXML()
{
var XMLFrag = ""
var hive = "HKLM";
var key = "SOFTWARE\\Microsoft\\MSSQLServer\\Client\\Connect To";
aAliasValues = HKLM_EnumValue(key);
if (aAliasValues != null)
{
var WshShell = WScript.CreateObject ("WScript.Shell");
var buffer = "";
XMLFrag += " <aliaslist>\n";
for (var x=0; x < aAliasValues.length; x++)
{
buffer = WshShell.RegRead (hive + "\\" + key + "\\" + aAliasValues[x]);
XMLFrag += " <alias name='" + aAliasValues[x] + "' data='" + buffer + "'/>\n";
}
XMLFrag += " </aliaslist>\n";
}
return (XMLFrag);
}
/*
|| Pass path to registry Key in HKLM
|| Returns an array with the values in the key
|| null if key is not found
||
*/
function HKLM_EnumValue (regPath)
{
HKLM = 0x80000002;
sRegPath = regPath;
try
{
oLoc = new ActiveXObject("WbemScripting.SWbemLocator");
oSvc = oLoc.ConnectServer(null, "root\\default");
oReg = oSvc.Get("StdRegProv");
oMethod = oReg.Methods_.Item("EnumValues");
oInParam = oMethod.InParameters.SpawnInstance_();
oInParam.hDefKey = HKLM;
oInParam.sSubKeyName = sRegPath;
oOutParam = oReg.ExecMethod_(oMethod.Name, oInParam);
if (oOutParam.sNames != null)
return (oOutParam.sNames.toArray());
else
return (null);
}
catch(err)
{
WScript.Echo("Error occurred\nCode: " + hex(err.number) +"; Description: " + err.description);
}
}
//User-defined function to format error codes.
//VBScript has a Hex() function but JScript does not.
function hex(nmb)
{
if (nmb > 0)
return nmb.toString(16);
else
return (nmb + 0x100000000).toString(16);
}
// Strip out special chars
function XMLEncode(str)
{
return (
(str == "<") ? "<" :
(str == ">") ? ">" :
(str == "&") ? "&" :
(str == "'") ? "'" :
(str == "\"") ? """ : str
);
}
//*************************************************//
//*************************************************//
// Load_SQL_Reg.js
// open the xml file
var fname = "servers.xml";
var xmlDoc = new ActiveXObject("Msxml2.DOMDocument");
xmlDoc.async = false;
xmlDoc.resolveExternals = false;
xmlDoc.load(fname);
if (xmlDoc.parseError.errorCode != 0)
{
var myErr = xmlDoc.parseError;
WScript.Echo("You have error " + myErr.reason);
}
else
{
// process each alias
var objNodeList = xmlDoc.getElementsByTagName("alias");
for (var i=0; i < objNodeList.length; i++)
{
var alias = objNodeList.item(i).getAttribute("name");
var data = objNodeList.item(i).getAttribute("data");
addAlias (alias, data);
}
var oApplication = new ActiveXObject("SQLDMO.Application");
var oServerGroups = oApplication.ServerGroups;
var oRegisteredServers = oApplication.RegisteredServers;
var objNodeList = xmlDoc.getElementsByTagName("reg");
for (var i=0; i < objNodeList.length; i++)
{
// Test the group
var group = objNodeList.item(i).parentNode.getAttribute("name" );
try
{
// does the group already exist?
var oServerGroup = oServerGroups(group);
}
catch (e)
{
// add it
WScript.Echo("Adding group: " + group);
var oGroup = new ActiveXObject("SQLDMO.ServerGroup");
oGroup.Name = group;
oServerGroups.Add(oGroup);
}
// Test the server
server = objNodeList.item(i).getAttribute("server");
try
{// Does the server already exist in this group?
var oRegisteredServer = oServerGroups(group).RegisteredServers(server);
// If so delete it!
WScript.Echo ("Removing Server: " + server);
oServerGroups(group).RegisteredServers.Remove(serv er);
}
catch (e)
{
// does not exist, do nothing
}
try
{
// NOTE: this will fail if server is already registered in a different group
WScript.Echo ("Adding Server: " + server);
var oNewServer = new ActiveXObject("SQLDMO.RegisteredServer");
oNewServer.Name = server;
oNewServer.Login = objNodeList.item(i).getAttribute("login").replace( /^$/, " ");
oNewServer.Password = objNodeList.item(i).getAttribute("password");
oNewServer.UseTrustedConnection = parseInt (objNodeList.item(i).getAttribute("trustedyn"));
oServerGroups(group).RegisteredServers.Add(oNewSer ver);
}
catch (e)
{
// print error and continue
WScript.Echo ("***Error**: " + e.description);
}
}
}
function addAlias(alias, data)
{
var regKey = "HKLM\\SOFTWARE\\Microsoft\\MSSQLServer\\Client\\C onnectTo\\" + alias ;
// see if the key exists
var WshShell = WScript.CreateObject ("WScript.Shell");
try
{
var found = WshShell.RegRead (regKey);
}
catch (e)
{
//do nothing
}
if (found != null)
{
// delete the key before re-add
WshShell.RegDelete (regKey);
}
// add the key
WScript.Echo ("Adding alias for : " + regKey);
WshShell.RegWrite (regKey, data, "REG_SZ");
}
//*************************************************//
""Mingqing Cheng [MSFT]"" wrote:
> Hi Kevin,
> Does the script works fine with you? Based on my scope, I am afraid you
> could not find Server Aliases by means of SQLDMO. I think reading directly
> from Registry is fine.
> BTW, It is highly appreciated if you could share us with your JavaScript
> and XML files among Newsgroup Community. In this way, I believe, provided
> multi ways for those who may encounter the same issue. Thanks in advance.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
