Hi All,
I have just installed EM for SQL Server 2000 and so have no registered servers in the console list. We have about 50 servers which we support and so I would rather import my colleagues list rather than registering them all manually. Is there ay way to do this ?
Many thanks
AndyUSE THIS AT YOUR OWN RISK. Works for me !! I accept no responsibility for anything :)
Well, this is a solution I would not attribute to myself , coz i did never invent it myself.
Register all your servers in one Enterprise Manager.
Then go to these keys :
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\SuperSocketNetLib\LastConnect]
[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X]
and export them.
Share the .reg files with your colleagues. Enjoy|||Enigma,
Thanks a lot... this worked a treat.
Cheers
Andysql
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Migrating db's from 2000 to 2005 results in truncated columns?
Hi all,
Today I stumbled across something very strange. A couple weeks ago we migrated 2 servers from sql2000 to sql2005, and changed the default colation at the same time. The way I did it was I backed up all the user databases to *.bak files, uninstalled sql2000, installed sql2005 using the new default colation, and restored the databases. Today we discovered any columns that used to be char(xxx) were truncated to char(255), and the leftover went into a new column (i.e. a char(300) column became 2 columns, column1 char(255) and column2 char(45)).
Does this remotely make sense to anyone? I tested this out creating a dummy database and going from a 2k to 2k5 instance with the same colations would not split the columns, however from 2k to 2k5 with a different colation does. And so far it only appears to have affected the char datatype.
(note this is from sql2k sp3a to sql2k5 without sp1)
Is this a bug or am I just whacked?
Thanks
Anyone?|||I'm moving this thread to the database engine forum, where you're more likely to get help.
Paul
Monday, March 26, 2012
migrating a SQL Cluster to new hardware
I need your advise on migrating a two node SQL 2000 cluster to new hardware
including servers and disks.
thanks.
Amila
Create a new cluster with the new hardware, install a SQL instance, and then
migrate the databases over to the new instance (e.g. following steps in
http://vyaskn.tripod.com/moving_sql_server.htm and its referenced articles).
If the shared drives are presented from a SAN, you can remove the drives
from the cluster, create a new cluster, install a SQL instance in exactly the
same way as the existing SQL instance, and present the shared drives to the
new cluster.
If you can afford the downtime, and want to keep the server names, you can
remove the shared drives (LUNs), deactivate the cluster nodes, create a new
cluster with the same node names, cluster names, and virtual server names,
install a SQL instance, and present the same LUNs to the new cluster. The new
SQL instance can come up exactly the same as the previous one.
You may also try to perform the so-called rolling upgrade, i.e. upgrade one
node to new hardware a time.
Which method to choose depends on your upgrade requirements.
Linchi
"Amila chandrasekera" wrote:
> I need your advise on migrating a two node SQL 2000 cluster to new hardware
> including servers and disks.
>
> thanks.
> Amila
>
>
|||We have done both of what Linchi suggested: new system build/disk migration
versus node rebuild/upgrade.
The new cluster build is cleaner/easier, but then your applications have to
be migrated to the new host name (unless you can incur the long outage).
The node rebuild/upgrade is not quite as clean, but it minimizes the impact
to your end users.
Check out the maintenance and troubleshooting sections of the following
guide. There is also a link at the top that directs you to the 2005 doc as
well. Read both.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx#E14AG
The short sequence is:
Uninstall passive node from the sql server setup.
Evict passive node from cluster administrator.
Rebuild passive node.
Join rebuilt node through cluster administrator.
Install rebuilt node to sql server setup.
Patch sql server rebuilt node to current patch + hotfix as active node.
Move over resources and repeat for each cluster node.
Sincerely,
Anthony Thomas
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:%23h6t5u7SHHA.412@.TK2MSFTNGP02.phx.gbl...
> I need your advise on migrating a two node SQL 2000 cluster to new
hardware
> including servers and disks.
>
> thanks.
> Amila
>
|||Run upgrade advisor to detect upgrade issues, run checkdb on the databases
on the clusters, detach them, copy them (do not move them) to the new
cluster, attach them, run checkdb, update statistics, back them up. Migrate
all dependencies (logins, jobs, extended stored procedures, com objects and
file paths).
Check all functionality on the new cluster. Roll back if you have to by
reattaching the existing database on the old cluster.
repoint the client applications.
You might want to review all methods to do this in the
http://download.microsoft.com/download/1/6/c/16c0ec7a-bb53-4aea-9020-cb2f80424322/SQL2005UpgradeTechRef.doc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:%23h6t5u7SHHA.412@.TK2MSFTNGP02.phx.gbl...
>I need your advise on migrating a two node SQL 2000 cluster to new hardware
>including servers and disks.
>
> thanks.
> Amila
>
|||Thanks for the replies so far. This migration is from SQL 2000 to SQL 2000
(both source and target are running on Windows 2003 Advanced server). I am
not planning to do a SQL 2005 upgrade with this migration. It is purely a
hardware upgrade only.
Here is what I am thinking of doing.
Asume Node1 and Node2 are the current production SQL cluster nodes.
Node3 and Node4 are the new servers.
1. Move all cluster roles to Node1
2. Evict node2 from the cluster
3. Conect Node3 and join it to the cluster
4. Move all cluster roles to Node3
5. Evict Node1 from cluster
6. Connect Node4 and join it to the cluster
7. Connect Node3 and Node4 to the new SAN (while connected to the old SAN )
8. Setup Quorum and data stores in the new SAN
9. Move Quorum to the new SAN
10. Shutdown the SQL Server
11. Copy SQL DBs from old SAN to the new SAN. Change Drive letter mappings
to maintain the paths.
12. Disconnect old SAN connection completely.
13. Start SQL Server
Is this feasible ? Am I missing anything here ?
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:%23h6t5u7SHHA.412@.TK2MSFTNGP02.phx.gbl...
>I need your advise on migrating a two node SQL 2000 cluster to new hardware
>including servers and disks.
>
> thanks.
> Amila
>
|||Before eviting the nodes, you have to run the SQL installer to remove the
node from the SQL cluster configuration. You cn run it afterwards and force
it out, but that takes longer. You also have to run the installer and
install SQL to the new nodes. You then need to re-apply any service packs.
GNH
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:OGAFgGFUHHA.1212@.TK2MSFTNGP03.phx.gbl...
> Thanks for the replies so far. This migration is from SQL 2000 to SQL 2000
> (both source and target are running on Windows 2003 Advanced server). I am
> not planning to do a SQL 2005 upgrade with this migration. It is purely a
> hardware upgrade only.
> Here is what I am thinking of doing.
> Asume Node1 and Node2 are the current production SQL cluster nodes.
> Node3 and Node4 are the new servers.
> 1. Move all cluster roles to Node1
> 2. Evict node2 from the cluster
> 3. Conect Node3 and join it to the cluster
> 4. Move all cluster roles to Node3
> 5. Evict Node1 from cluster
> 6. Connect Node4 and join it to the cluster
> 7. Connect Node3 and Node4 to the new SAN (while connected to the old
> SAN )
> 8. Setup Quorum and data stores in the new SAN
> 9. Move Quorum to the new SAN
> 10. Shutdown the SQL Server
> 11. Copy SQL DBs from old SAN to the new SAN. Change Drive letter mappings
> to maintain the paths.
> 12. Disconnect old SAN connection completely.
> 13. Start SQL Server
> Is this feasible ? Am I missing anything here ?
>
> "Amila chandrasekera" <achandra@.insight.com> wrote in message
> news:%23h6t5u7SHHA.412@.TK2MSFTNGP02.phx.gbl...
>
|||How Can I remove SQL after evicting the node ?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OQ5q9KFUHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Before eviting the nodes, you have to run the SQL installer to remove the
> node from the SQL cluster configuration. You cn run it afterwards and
> force it out, but that takes longer. You also have to run the installer
> and install SQL to the new nodes. You then need to re-apply any service
> packs.
> GNH
>
> "Amila chandrasekera" <achandra@.insight.com> wrote in message
> news:OGAFgGFUHHA.1212@.TK2MSFTNGP03.phx.gbl...
>
|||Go ahead and run the installer on the remaining node. When you remove the
evicted node from SQL the installer will time out and isue a warning, but it
will remove it from the SQL configuration.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:e9erf1pVHHA.3568@.TK2MSFTNGP06.phx.gbl...
> How Can I remove SQL after evicting the node ?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OQ5q9KFUHHA.1180@.TK2MSFTNGP05.phx.gbl...
>
|||You can also remove it manually, but the Best Practice is to use the SQL
Server setup instead.
http://support.microsoft.com/kb/290991/en-us
Sincerely,
Anthony Thomas
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:O84Iu3qVHHA.4964@.TK2MSFTNGP06.phx.gbl...
> Go ahead and run the installer on the remaining node. When you remove the
> evicted node from SQL the installer will time out and isue a warning, but
it[vbcol=seagreen]
> will remove it from the SQL configuration.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Amila chandrasekera" <achandra@.insight.com> wrote in message
> news:e9erf1pVHHA.3568@.TK2MSFTNGP06.phx.gbl...
the[vbcol=seagreen]
installer[vbcol=seagreen]
service[vbcol=seagreen]
migration.
>
including servers and disks.
thanks.
Amila
Create a new cluster with the new hardware, install a SQL instance, and then
migrate the databases over to the new instance (e.g. following steps in
http://vyaskn.tripod.com/moving_sql_server.htm and its referenced articles).
If the shared drives are presented from a SAN, you can remove the drives
from the cluster, create a new cluster, install a SQL instance in exactly the
same way as the existing SQL instance, and present the shared drives to the
new cluster.
If you can afford the downtime, and want to keep the server names, you can
remove the shared drives (LUNs), deactivate the cluster nodes, create a new
cluster with the same node names, cluster names, and virtual server names,
install a SQL instance, and present the same LUNs to the new cluster. The new
SQL instance can come up exactly the same as the previous one.
You may also try to perform the so-called rolling upgrade, i.e. upgrade one
node to new hardware a time.
Which method to choose depends on your upgrade requirements.
Linchi
"Amila chandrasekera" wrote:
> I need your advise on migrating a two node SQL 2000 cluster to new hardware
> including servers and disks.
>
> thanks.
> Amila
>
>
|||We have done both of what Linchi suggested: new system build/disk migration
versus node rebuild/upgrade.
The new cluster build is cleaner/easier, but then your applications have to
be migrated to the new host name (unless you can incur the long outage).
The node rebuild/upgrade is not quite as clean, but it minimizes the impact
to your end users.
Check out the maintenance and troubleshooting sections of the following
guide. There is also a link at the top that directs you to the 2005 doc as
well. Read both.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx#E14AG
The short sequence is:
Uninstall passive node from the sql server setup.
Evict passive node from cluster administrator.
Rebuild passive node.
Join rebuilt node through cluster administrator.
Install rebuilt node to sql server setup.
Patch sql server rebuilt node to current patch + hotfix as active node.
Move over resources and repeat for each cluster node.
Sincerely,
Anthony Thomas
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:%23h6t5u7SHHA.412@.TK2MSFTNGP02.phx.gbl...
> I need your advise on migrating a two node SQL 2000 cluster to new
hardware
> including servers and disks.
>
> thanks.
> Amila
>
|||Run upgrade advisor to detect upgrade issues, run checkdb on the databases
on the clusters, detach them, copy them (do not move them) to the new
cluster, attach them, run checkdb, update statistics, back them up. Migrate
all dependencies (logins, jobs, extended stored procedures, com objects and
file paths).
Check all functionality on the new cluster. Roll back if you have to by
reattaching the existing database on the old cluster.
repoint the client applications.
You might want to review all methods to do this in the
http://download.microsoft.com/download/1/6/c/16c0ec7a-bb53-4aea-9020-cb2f80424322/SQL2005UpgradeTechRef.doc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:%23h6t5u7SHHA.412@.TK2MSFTNGP02.phx.gbl...
>I need your advise on migrating a two node SQL 2000 cluster to new hardware
>including servers and disks.
>
> thanks.
> Amila
>
|||Thanks for the replies so far. This migration is from SQL 2000 to SQL 2000
(both source and target are running on Windows 2003 Advanced server). I am
not planning to do a SQL 2005 upgrade with this migration. It is purely a
hardware upgrade only.
Here is what I am thinking of doing.
Asume Node1 and Node2 are the current production SQL cluster nodes.
Node3 and Node4 are the new servers.
1. Move all cluster roles to Node1
2. Evict node2 from the cluster
3. Conect Node3 and join it to the cluster
4. Move all cluster roles to Node3
5. Evict Node1 from cluster
6. Connect Node4 and join it to the cluster
7. Connect Node3 and Node4 to the new SAN (while connected to the old SAN )
8. Setup Quorum and data stores in the new SAN
9. Move Quorum to the new SAN
10. Shutdown the SQL Server
11. Copy SQL DBs from old SAN to the new SAN. Change Drive letter mappings
to maintain the paths.
12. Disconnect old SAN connection completely.
13. Start SQL Server
Is this feasible ? Am I missing anything here ?
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:%23h6t5u7SHHA.412@.TK2MSFTNGP02.phx.gbl...
>I need your advise on migrating a two node SQL 2000 cluster to new hardware
>including servers and disks.
>
> thanks.
> Amila
>
|||Before eviting the nodes, you have to run the SQL installer to remove the
node from the SQL cluster configuration. You cn run it afterwards and force
it out, but that takes longer. You also have to run the installer and
install SQL to the new nodes. You then need to re-apply any service packs.
GNH
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:OGAFgGFUHHA.1212@.TK2MSFTNGP03.phx.gbl...
> Thanks for the replies so far. This migration is from SQL 2000 to SQL 2000
> (both source and target are running on Windows 2003 Advanced server). I am
> not planning to do a SQL 2005 upgrade with this migration. It is purely a
> hardware upgrade only.
> Here is what I am thinking of doing.
> Asume Node1 and Node2 are the current production SQL cluster nodes.
> Node3 and Node4 are the new servers.
> 1. Move all cluster roles to Node1
> 2. Evict node2 from the cluster
> 3. Conect Node3 and join it to the cluster
> 4. Move all cluster roles to Node3
> 5. Evict Node1 from cluster
> 6. Connect Node4 and join it to the cluster
> 7. Connect Node3 and Node4 to the new SAN (while connected to the old
> SAN )
> 8. Setup Quorum and data stores in the new SAN
> 9. Move Quorum to the new SAN
> 10. Shutdown the SQL Server
> 11. Copy SQL DBs from old SAN to the new SAN. Change Drive letter mappings
> to maintain the paths.
> 12. Disconnect old SAN connection completely.
> 13. Start SQL Server
> Is this feasible ? Am I missing anything here ?
>
> "Amila chandrasekera" <achandra@.insight.com> wrote in message
> news:%23h6t5u7SHHA.412@.TK2MSFTNGP02.phx.gbl...
>
|||How Can I remove SQL after evicting the node ?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OQ5q9KFUHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Before eviting the nodes, you have to run the SQL installer to remove the
> node from the SQL cluster configuration. You cn run it afterwards and
> force it out, but that takes longer. You also have to run the installer
> and install SQL to the new nodes. You then need to re-apply any service
> packs.
> GNH
>
> "Amila chandrasekera" <achandra@.insight.com> wrote in message
> news:OGAFgGFUHHA.1212@.TK2MSFTNGP03.phx.gbl...
>
|||Go ahead and run the installer on the remaining node. When you remove the
evicted node from SQL the installer will time out and isue a warning, but it
will remove it from the SQL configuration.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Amila chandrasekera" <achandra@.insight.com> wrote in message
news:e9erf1pVHHA.3568@.TK2MSFTNGP06.phx.gbl...
> How Can I remove SQL after evicting the node ?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OQ5q9KFUHHA.1180@.TK2MSFTNGP05.phx.gbl...
>
|||You can also remove it manually, but the Best Practice is to use the SQL
Server setup instead.
http://support.microsoft.com/kb/290991/en-us
Sincerely,
Anthony Thomas
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:O84Iu3qVHHA.4964@.TK2MSFTNGP06.phx.gbl...
> Go ahead and run the installer on the remaining node. When you remove the
> evicted node from SQL the installer will time out and isue a warning, but
it[vbcol=seagreen]
> will remove it from the SQL configuration.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Amila chandrasekera" <achandra@.insight.com> wrote in message
> news:e9erf1pVHHA.3568@.TK2MSFTNGP06.phx.gbl...
the[vbcol=seagreen]
installer[vbcol=seagreen]
service[vbcol=seagreen]
migration.
>
Migrating a database
We have a database running under SQL Server 7 on a Win NT server. We
have recently added Win2K servers, created a new domain and installed
SQL Server 7 on one of the servers. I would like to be able to log into
either domain from a workstation, use Enterprise Manager to access
either database and use the migration tools to copy the database from
the NT server to the 2K server.
We have created a trust relationship that allows me to log into either
domain, but when I try to use Enterprise Manager to create a
registration to the database on the other domain, or try to use the
migration tool to copy the database, I get an error saying that my user
account cannot log into the other system. ( I have the same user name
and password on both domains and domain admin privileges).
Can this be done directly? How?
Do I need "linked servers"? -- It doesn't like my account to create this
either.
Do I need a "remote server"?
Reading the documentation creates more questions than answers. I would
appreciate someone pointing me in the right direction. ThanksWhat is the exact error you are seeing and which domain are you logging
into, the new one or the other one.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||When I'm logged into the NT domain and trying to register SQL Server on the 2K
server, I get "login failed for user 'patenaude'".
When I'm logged into the 2K domain and trying to register SQL server on the NT
server, I get " loged failed for user 'null'. Not associated with a trusted
SQL Server connection."
I get similar messages trying to set up a linked server.
"Rand Boyd [MS]" wrote:
> What is the exact error you are seeing and which domain are you logging
> into, the new one or the other one.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.|||When you are logged into the Windows NT domain are you trying to register
the SQL Server using NT or SQL authentication. The error indicates SQL
authentication. If so does the 'patenaude' SQL login exist? Is the password
correct?
When you are logging into the Win 2K domain, the error indicates that you
cannot be validated on the domain controller. That is where the null is
coming from. Check the security log of the domain controller for this
domain for errors related to this account. Also if you are logged on to the
workstation using a local account you will see this error.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I changed the system on the 2K server to use NT authentication, so now I get
the same error message about my user account not being valid. (The user id is
good, the password is good.) My workstation is a member of the NT domain and
the trust relationship is set up to the point where I can log into either
domain. The user name is the same on both domains. I have tried registering
across the domain from both directions, using the default log-in with NT
authentication, using the specific login with my user name and using the
specific log-in with each of the domains added ( NTdomain\user & 2Kdomain\user)
I get the same results each time. I looked in the event logs on the servers and
there is no mention of the attempt in any of the logs.
"Rand Boyd [MS]" wrote:
> When you are logged into the Windows NT domain are you trying to register
> the SQL Server using NT or SQL authentication. The error indicates SQL
> authentication. If so does the 'patenaude' SQL login exist? Is the password
> correct?
> When you are logging into the Win 2K domain, the error indicates that you
> cannot be validated on the domain controller. That is where the null is
> coming from. Check the security log of the domain controller for this
> domain for errors related to this account. Also if you are logged on to the
> workstation using a local account you will see this error.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
have recently added Win2K servers, created a new domain and installed
SQL Server 7 on one of the servers. I would like to be able to log into
either domain from a workstation, use Enterprise Manager to access
either database and use the migration tools to copy the database from
the NT server to the 2K server.
We have created a trust relationship that allows me to log into either
domain, but when I try to use Enterprise Manager to create a
registration to the database on the other domain, or try to use the
migration tool to copy the database, I get an error saying that my user
account cannot log into the other system. ( I have the same user name
and password on both domains and domain admin privileges).
Can this be done directly? How?
Do I need "linked servers"? -- It doesn't like my account to create this
either.
Do I need a "remote server"?
Reading the documentation creates more questions than answers. I would
appreciate someone pointing me in the right direction. ThanksWhat is the exact error you are seeing and which domain are you logging
into, the new one or the other one.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||When I'm logged into the NT domain and trying to register SQL Server on the 2K
server, I get "login failed for user 'patenaude'".
When I'm logged into the 2K domain and trying to register SQL server on the NT
server, I get " loged failed for user 'null'. Not associated with a trusted
SQL Server connection."
I get similar messages trying to set up a linked server.
"Rand Boyd [MS]" wrote:
> What is the exact error you are seeing and which domain are you logging
> into, the new one or the other one.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.|||When you are logged into the Windows NT domain are you trying to register
the SQL Server using NT or SQL authentication. The error indicates SQL
authentication. If so does the 'patenaude' SQL login exist? Is the password
correct?
When you are logging into the Win 2K domain, the error indicates that you
cannot be validated on the domain controller. That is where the null is
coming from. Check the security log of the domain controller for this
domain for errors related to this account. Also if you are logged on to the
workstation using a local account you will see this error.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I changed the system on the 2K server to use NT authentication, so now I get
the same error message about my user account not being valid. (The user id is
good, the password is good.) My workstation is a member of the NT domain and
the trust relationship is set up to the point where I can log into either
domain. The user name is the same on both domains. I have tried registering
across the domain from both directions, using the default log-in with NT
authentication, using the specific login with my user name and using the
specific log-in with each of the domains added ( NTdomain\user & 2Kdomain\user)
I get the same results each time. I looked in the event logs on the servers and
there is no mention of the attempt in any of the logs.
"Rand Boyd [MS]" wrote:
> When you are logged into the Windows NT domain are you trying to register
> the SQL Server using NT or SQL authentication. The error indicates SQL
> authentication. If so does the 'patenaude' SQL login exist? Is the password
> correct?
> When you are logging into the Win 2K domain, the error indicates that you
> cannot be validated on the domain controller. That is where the null is
> coming from. Check the security log of the domain controller for this
> domain for errors related to this account. Also if you are logged on to the
> workstation using a local account you will see this error.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
Friday, March 23, 2012
Migrate two databases from diferente Server into one consolidate
Hi,
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio CamposWhat i do is create the same login names on the new server, I then go and ru
n
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.si
d
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest
).
> I Know how to backup and restore databases from one server to another, but
I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/feat...cle.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO|||the problem with this task is that it doesn't keep the original SID. I prefe
r using sp_help_revlogin
which is fund in the KB.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via droptable.com" <forum@.droptable.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.SQ
droptable.com...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
Message posted via http://www.droptable.com|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.SQ
droptable.com...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the postin
g
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.droptable.com|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
Message posted via http://www.droptable.com|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides co
mpared to
sp_help_revlogins...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.SQ
droptable.com...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.droptable.com
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio CamposWhat i do is create the same login names on the new server, I then go and ru
n
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.si
d
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest
).
> I Know how to backup and restore databases from one server to another, but
I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/feat...cle.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO|||the problem with this task is that it doesn't keep the original SID. I prefe
r using sp_help_revlogin
which is fund in the KB.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via droptable.com" <forum@.droptable.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.SQ
droptable.com...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
Message posted via http://www.droptable.com|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.SQ
droptable.com...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the postin
g
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.droptable.com|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
Message posted via http://www.droptable.com|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides co
mpared to
sp_help_revlogins...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.SQ
droptable.com...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.droptable.com
Migrate two databases from diferente Server into one consolidate
Hi,
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio Campos
What i do is create the same login names on the new server, I then go and run
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.sid
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest).
> I Know how to backup and restore databases from one server to another, but I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos
|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/featu...le.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos
|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO
|||the problem with this task is that it doesn't keep the original SID. I prefer using sp_help_revlogin
which is fund in the KB.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via droptable.com" <forum@.droptable.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.droptable.co m...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO
|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
Message posted via http://www.droptable.com
|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.droptable.co m...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the posting
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.droptable.com
|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
Message posted via http://www.droptable.com
|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides compared to
sp_help_revlogins...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.droptable.co m...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.droptable.com
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio Campos
What i do is create the same login names on the new server, I then go and run
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.sid
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest).
> I Know how to backup and restore databases from one server to another, but I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos
|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/featu...le.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos
|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO
|||the problem with this task is that it doesn't keep the original SID. I prefer using sp_help_revlogin
which is fund in the KB.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via droptable.com" <forum@.droptable.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.droptable.co m...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO
|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
Message posted via http://www.droptable.com
|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.droptable.co m...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the posting
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.droptable.com
|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
Message posted via http://www.droptable.com
|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides compared to
sp_help_revlogins...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.droptable.co m...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.droptable.com
Migrate two databases from diferente Server into one consolidate
Hi,
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio CamposWhat i do is create the same login names on the new server, I then go and run
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.sid
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest).
> I Know how to backup and restore databases from one server to another, but I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/features/mssql/article.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO|||the problem with this task is that it doesn't keep the original SID. I prefer using sp_help_revlogin
which is fund in the KB.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.SQLMonster.com...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
--
Message posted via http://www.sqlmonster.com|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.SQLMonster.com...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the posting
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.sqlmonster.com|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
--
Message posted via http://www.sqlmonster.com|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides compared to
sp_help_revlogins...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.SQLMonster.com...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.sqlmonster.comsql
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio CamposWhat i do is create the same login names on the new server, I then go and run
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.sid
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest).
> I Know how to backup and restore databases from one server to another, but I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/features/mssql/article.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO|||the problem with this task is that it doesn't keep the original SID. I prefer using sp_help_revlogin
which is fund in the KB.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.SQLMonster.com...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
--
Message posted via http://www.sqlmonster.com|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.SQLMonster.com...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the posting
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.sqlmonster.com|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
--
Message posted via http://www.sqlmonster.com|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides compared to
sp_help_revlogins...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.SQLMonster.com...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.sqlmonster.comsql
migrate to cluster
I need to migrate a non-clustered server up to a cluster. Usually I use this
resource to migrate servers but have not tried it with a cluster
http://vyaskn.tripod.com/moving_sql_server.htm
Does anyone see any issues with using this to migrate to a cluster form a
non-clustered server?
Thanks.Hi
"Terri" wrote:
> I need to migrate a non-clustered server up to a cluster. Usually I use th
is
> resource to migrate servers but have not tried it with a cluster
> http://vyaskn.tripod.com/moving_sql_server.htm
> Does anyone see any issues with using this to migrate to a cluster form a
> non-clustered server?
> Thanks.
The instance on the cluster can in be considered in the same way as any
different instance when transferring a database see
http://support.microsoft.com/default.aspx/kb/314546
including moving logins/jobs etc.. and matching orphaned users.
I assume that you are not going to try and give the virtual server the same
name as the actual machine as it would a different process to the article yo
u
posted.
Johnsql
resource to migrate servers but have not tried it with a cluster
http://vyaskn.tripod.com/moving_sql_server.htm
Does anyone see any issues with using this to migrate to a cluster form a
non-clustered server?
Thanks.Hi
"Terri" wrote:
> I need to migrate a non-clustered server up to a cluster. Usually I use th
is
> resource to migrate servers but have not tried it with a cluster
> http://vyaskn.tripod.com/moving_sql_server.htm
> Does anyone see any issues with using this to migrate to a cluster form a
> non-clustered server?
> Thanks.
The instance on the cluster can in be considered in the same way as any
different instance when transferring a database see
http://support.microsoft.com/default.aspx/kb/314546
including moving logins/jobs etc.. and matching orphaned users.
I assume that you are not going to try and give the virtual server the same
name as the actual machine as it would a different process to the article yo
u
posted.
Johnsql
migrate to cluster
I need to migrate a non-clustered server up to a cluster. Usually I use this
resource to migrate servers but have not tried it with a cluster
http://vyaskn.tripod.com/moving_sql_server.htm
Does anyone see any issues with using this to migrate to a cluster form a
non-clustered server?
Thanks.
Hi
"Terri" wrote:
> I need to migrate a non-clustered server up to a cluster. Usually I use this
> resource to migrate servers but have not tried it with a cluster
> http://vyaskn.tripod.com/moving_sql_server.htm
> Does anyone see any issues with using this to migrate to a cluster form a
> non-clustered server?
> Thanks.
The instance on the cluster can in be considered in the same way as any
different instance when transferring a database see
http://support.microsoft.com/default.aspx/kb/314546
including moving logins/jobs etc.. and matching orphaned users.
I assume that you are not going to try and give the virtual server the same
name as the actual machine as it would a different process to the article you
posted.
John
resource to migrate servers but have not tried it with a cluster
http://vyaskn.tripod.com/moving_sql_server.htm
Does anyone see any issues with using this to migrate to a cluster form a
non-clustered server?
Thanks.
Hi
"Terri" wrote:
> I need to migrate a non-clustered server up to a cluster. Usually I use this
> resource to migrate servers but have not tried it with a cluster
> http://vyaskn.tripod.com/moving_sql_server.htm
> Does anyone see any issues with using this to migrate to a cluster form a
> non-clustered server?
> Thanks.
The instance on the cluster can in be considered in the same way as any
different instance when transferring a database see
http://support.microsoft.com/default.aspx/kb/314546
including moving logins/jobs etc.. and matching orphaned users.
I assume that you are not going to try and give the virtual server the same
name as the actual machine as it would a different process to the article you
posted.
John
migrate to cluster
I need to migrate a non-clustered server up to a cluster. Usually I use this
resource to migrate servers but have not tried it with a cluster
http://vyaskn.tripod.com/moving_sql_server.htm
Does anyone see any issues with using this to migrate to a cluster form a
non-clustered server?
Thanks.Hi
"Terri" wrote:
> I need to migrate a non-clustered server up to a cluster. Usually I use this
> resource to migrate servers but have not tried it with a cluster
> http://vyaskn.tripod.com/moving_sql_server.htm
> Does anyone see any issues with using this to migrate to a cluster form a
> non-clustered server?
> Thanks.
The instance on the cluster can in be considered in the same way as any
different instance when transferring a database see
http://support.microsoft.com/default.aspx/kb/314546
including moving logins/jobs etc.. and matching orphaned users.
I assume that you are not going to try and give the virtual server the same
name as the actual machine as it would a different process to the article you
posted.
John
resource to migrate servers but have not tried it with a cluster
http://vyaskn.tripod.com/moving_sql_server.htm
Does anyone see any issues with using this to migrate to a cluster form a
non-clustered server?
Thanks.Hi
"Terri" wrote:
> I need to migrate a non-clustered server up to a cluster. Usually I use this
> resource to migrate servers but have not tried it with a cluster
> http://vyaskn.tripod.com/moving_sql_server.htm
> Does anyone see any issues with using this to migrate to a cluster form a
> non-clustered server?
> Thanks.
The instance on the cluster can in be considered in the same way as any
different instance when transferring a database see
http://support.microsoft.com/default.aspx/kb/314546
including moving logins/jobs etc.. and matching orphaned users.
I assume that you are not going to try and give the virtual server the same
name as the actual machine as it would a different process to the article you
posted.
John
Migrate SQL Server from 64Bit platform to 32Bit
Hi, All:
We have to migrate several 64Bit SQL Servers to 32Bit platform. Due to the 64Bit source systems are all production system, the downtime is too limited. So we can only get the DB files via Detach way. Among the source systems, both SQL2000 and SQL2005 exist.
My question are:
Is it possible to migrate the DB from 64Bit to 32Bit via Detach/Attach?
If ok, how to do that?
Any info will be highly appreciated! Thanks!
Best Regards,
Kevin
See the response to the same question here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1066278&SiteID=1
Migrate SQL Server from 64Bit platform to 32Bit
Hi, All:
We have to migrate several 64Bit SQL Servers to 32Bit platform. Due to the 64Bit source systems are all production system, the downtime is too limited. So we can only get the DB files via Detach way. Among the source systems, both SQL2000 and SQL2005 exist.
My question are:
Is it possible to migrate the DB from 64Bit to 32Bit via Detach/Attach?
If ok, how to do that?
Any info will be highly appreciated! Thanks!
Best Regards,
Kevin
Hi Kevin.
The on-disk layout of database files is the same for 64bit and 32bit databases, there's no difference...you can freely attach/detach, backup/restore, etc. to/from 32bit/64bit systems and vice-versa at will with no issues. Of course, this assumes you are moving the between supported migration/compatibility instances...i.e. you couldn't move a Sql 2005 64bit database to a Sql 2000 32bit system...
HTH
|||Hi, Chad:
Greate thanks for your reply!
Best Regards,
Kevin
Migrate SQL 2000 to SQL 2005
We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
I don't mind doing an un-install or SQL 2000 then a clean install of SQL
2005 x64 but my problem is a large number of SQL logins
Is there any easy way to export SQL logins and passwords from SQL2000 and
import them into SQL2005?
Thanks
Peter Lawton
Hi
This can be run on your SQL 2000 installation
http://support.microsoft.com/kb/2461...22120121120120
John
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
> I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
> upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
> I don't mind doing an un-install or SQL 2000 then a clean install of SQL
> 2005 x64 but my problem is a large number of SQL logins
> Is there any easy way to export SQL logins and passwords from SQL2000 and
> import them into SQL2005?
> Thanks
> Peter Lawton
>
|||mant thanks - that looks like just what I need!
Peter Lawton
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ehKcwoUCGHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi
> This can be run on your SQL 2000 installation
> http://support.microsoft.com/kb/2461...22120121120120
> John
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
>
I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
I don't mind doing an un-install or SQL 2000 then a clean install of SQL
2005 x64 but my problem is a large number of SQL logins
Is there any easy way to export SQL logins and passwords from SQL2000 and
import them into SQL2005?
Thanks
Peter Lawton
Hi
This can be run on your SQL 2000 installation
http://support.microsoft.com/kb/2461...22120121120120
John
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
> I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
> upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
> I don't mind doing an un-install or SQL 2000 then a clean install of SQL
> 2005 x64 but my problem is a large number of SQL logins
> Is there any easy way to export SQL logins and passwords from SQL2000 and
> import them into SQL2005?
> Thanks
> Peter Lawton
>
|||mant thanks - that looks like just what I need!
Peter Lawton
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ehKcwoUCGHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi
> This can be run on your SQL 2000 installation
> http://support.microsoft.com/kb/2461...22120121120120
> John
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
>
Migrate SQL 2000 to SQL 2005
We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
I don't mind doing an un-install or SQL 2000 then a clean install of SQL
2005 x64 but my problem is a large number of SQL logins
Is there any easy way to export SQL logins and passwords from SQL2000 and
import them into SQL2005?
Thanks
Peter LawtonHi
This can be run on your SQL 2000 installation
http://support.microsoft.com/kb/246133/#XSLTH3145121122120121120120
John
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
> I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
> upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
> I don't mind doing an un-install or SQL 2000 then a clean install of SQL
> 2005 x64 but my problem is a large number of SQL logins
> Is there any easy way to export SQL logins and passwords from SQL2000 and
> import them into SQL2005?
> Thanks
> Peter Lawton
>|||mant thanks - that looks like just what I need!
Peter Lawton
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ehKcwoUCGHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi
> This can be run on your SQL 2000 installation
> http://support.microsoft.com/kb/246133/#XSLTH3145121122120121120120
> John
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
>> We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
>> I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I
>> expect upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
>> I don't mind doing an un-install or SQL 2000 then a clean install of SQL
>> 2005 x64 but my problem is a large number of SQL logins
>> Is there any easy way to export SQL logins and passwords from SQL2000 and
>> import them into SQL2005?
>> Thanks
>> Peter Lawton
>
I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
I don't mind doing an un-install or SQL 2000 then a clean install of SQL
2005 x64 but my problem is a large number of SQL logins
Is there any easy way to export SQL logins and passwords from SQL2000 and
import them into SQL2005?
Thanks
Peter LawtonHi
This can be run on your SQL 2000 installation
http://support.microsoft.com/kb/246133/#XSLTH3145121122120121120120
John
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
> I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
> upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
> I don't mind doing an un-install or SQL 2000 then a clean install of SQL
> 2005 x64 but my problem is a large number of SQL logins
> Is there any easy way to export SQL logins and passwords from SQL2000 and
> import them into SQL2005?
> Thanks
> Peter Lawton
>|||mant thanks - that looks like just what I need!
Peter Lawton
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ehKcwoUCGHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi
> This can be run on your SQL 2000 installation
> http://support.microsoft.com/kb/246133/#XSLTH3145121122120121120120
> John
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
>> We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
>> I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I
>> expect upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
>> I don't mind doing an un-install or SQL 2000 then a clean install of SQL
>> 2005 x64 but my problem is a large number of SQL logins
>> Is there any easy way to export SQL logins and passwords from SQL2000 and
>> import them into SQL2005?
>> Thanks
>> Peter Lawton
>
Wednesday, March 21, 2012
Migrate SQL 2000 to SQL 2005
We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
I don't mind doing an un-install or SQL 2000 then a clean install of SQL
2005 x64 but my problem is a large number of SQL logins
Is there any easy way to export SQL logins and passwords from SQL2000 and
import them into SQL2005?
Thanks
Peter LawtonHi
This can be run on your SQL 2000 installation
http://support.microsoft.com/kb/246...122120121120120
John
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
> I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
> upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
> I don't mind doing an un-install or SQL 2000 then a clean install of SQL
> 2005 x64 but my problem is a large number of SQL logins
> Is there any easy way to export SQL logins and passwords from SQL2000 and
> import them into SQL2005?
> Thanks
> Peter Lawton
>|||mant thanks - that looks like just what I need!
Peter Lawton
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ehKcwoUCGHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi
> This can be run on your SQL 2000 installation
> http://support.microsoft.com/kb/246...122120121120120
> John
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
>
I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
I don't mind doing an un-install or SQL 2000 then a clean install of SQL
2005 x64 but my problem is a large number of SQL logins
Is there any easy way to export SQL logins and passwords from SQL2000 and
import them into SQL2005?
Thanks
Peter LawtonHi
This can be run on your SQL 2000 installation
http://support.microsoft.com/kb/246...122120121120120
John
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> We want to migrate some x64 servers from 32bit SQL 2000 to 64bit SQL 2005
> I know that upgrading SQL 2000 to x64 SQL 2005 isn't possible and I expect
> upgrading x32 SQL2005 to x64 SQL2005 isn't possible either.
> I don't mind doing an un-install or SQL 2000 then a clean install of SQL
> 2005 x64 but my problem is a large number of SQL logins
> Is there any easy way to export SQL logins and passwords from SQL2000 and
> import them into SQL2005?
> Thanks
> Peter Lawton
>|||mant thanks - that looks like just what I need!
Peter Lawton
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ehKcwoUCGHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi
> This can be run on your SQL 2000 installation
> http://support.microsoft.com/kb/246...122120121120120
> John
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:uktsVlHCGHA.4040@.TK2MSFTNGP12.phx.gbl...
>
migrate objects between SQL2005
I got 2 development servers, I m triyng to move some data from 2 tables to other tables from other database located in other server.
I was using Sql Server 2005 Business Intelligence Development Studio to acommplish this, but when i debug it is shows me error.
someone can tell me the right steps to do this?
If you are using a windows authenticated account to access both servers, just right-click the table in the source database, go to Script Table as --> CREATE to --> New query editor window.
Once the script is generated, right-click in your script and go to Connection -->Change Connection, and connect to the other SQL Server. Once connected, select the appropriate database, and run the create script to add the table.
Then you can run a simple insert statement to post records from the source table to the new table using fully qulified naming. For example:
Insert into TargetTableName
Select * from SourceServerName.SourceDatabase.dbo.SourceTableName
migrate linked servers with passwords
This is one that I've seen asked often but without a definitive answer:
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but
I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:
> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a differen
t
> server. I can get this to work on the same server with the script below bu
t I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but
I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:
> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a differen
t
> server. I can get this to work on the same server with the script below bu
t I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go
migrate linked servers with passwords
This is one that I've seen asked often but without a definitive answer:
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:
> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a different
> server. I can get this to work on the same server with the script below but I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:
> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a different
> server. I can get this to work on the same server with the script below but I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go
migrate linked servers
Hello everyone,
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
Chieko
Hi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>
|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> sql
>
|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during[vbcol=seagreen]
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
result[vbcol=seagreen]
> to
> credentials
on
>
sql
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
Chieko
Hi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>
|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> sql
>
|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during[vbcol=seagreen]
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
result[vbcol=seagreen]
> to
> credentials
on
>
sql
migrate linked servers
Hello everyone,
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
ChiekoHi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> > Hello everyone,
> > I'd like to learn how I should go about moving my linked servers from on
> sql
> > server to another sql server. Has anyone done this before?
> > Thanks
> > Chieko
> >
> >
>|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
--
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> >
> > Execute the script in query analyzer with text output and copy the
result
> to
> > destination server and execute it.
> >
> > (Script from old post)
> >
> > select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> > isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> > isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> > isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> > '')+char(10)+'go'
> > from master..sysservers
> >
> > This will help you to move the linked servers, but the security
> credentials
> > you need to define manually.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> > news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> > > Hello everyone,
> > > I'd like to learn how I should go about moving my linked servers from
on
> > sql
> > > server to another sql server. Has anyone done this before?
> > > Thanks
> > > Chieko
> > >
> > >
> >
> >
>
I'd like to learn how I should go about moving my linked servers from on sql
server to another sql server. Has anyone done this before?
Thanks
ChiekoHi,
Execute the script in query analyzer with text output and copy the result to
destination server and execute it.
(Script from old post)
select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
'')+char(10)+'go'
from master..sysservers
This will help you to move the linked servers, but the security credentials
you need to define manually.
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'd like to learn how I should go about moving my linked servers from on
sql
> server to another sql server. Has anyone done this before?
> Thanks
> Chieko
>|||Thanks, I figure out that if I backed up the master database on the old
server and restored it on the new server the linked servers were add during
the restore process.
Chieko
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Execute the script in query analyzer with text output and copy the result
to
> destination server and execute it.
> (Script from old post)
> select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> '')+char(10)+'go'
> from master..sysservers
> This will help you to move the linked servers, but the security
credentials
> you need to define manually.
> Thanks
> Hari
> MCDBA
> "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> > Hello everyone,
> > I'd like to learn how I should go about moving my linked servers from on
> sql
> > server to another sql server. Has anyone done this before?
> > Thanks
> > Chieko
> >
> >
>|||Hi,
OK, That is also a approch. I was under the impression that you just need to
move Linked servers only.
If you restore the Master database, it will load all logins, databases,
configurations,....If
your destination server is of same configuration as source then your
solution will work well.
If the configurations is different then the restore will cause the database
to go to suspect status and
might cause the service startup failure.
Since it works fine, no need to worry.
--
Thanks
Hari
MCDBA
"Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
news:u#D4GAxTEHA.156@.TK2MSFTNGP12.phx.gbl...
> Thanks, I figure out that if I backed up the master database on the old
> server and restored it on the new server the linked servers were add
during
> the restore process.
> Chieko
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eDRLL3vTEHA.504@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> >
> > Execute the script in query analyzer with text output and copy the
result
> to
> > destination server and execute it.
> >
> > (Script from old post)
> >
> > select 'exec sp_addlinkedserver @.server=''' + srvname + '''' +
> > isnull(', @.srvproduct=''' + nullif(srvproduct, '')+ '''', '') +
> > isnull(', @.provider=''' + nullif(providername, '')+ '''', '') +
> > isnull(', @.datasrc=''' + nullif(datasource, '')+ '''',
> > '')+char(10)+'go'
> > from master..sysservers
> >
> > This will help you to move the linked servers, but the security
> credentials
> > you need to define manually.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Chieko Kuroda" <ckuroda@.unch.unc.edu> wrote in message
> > news:#o#bX8uTEHA.1244@.TK2MSFTNGP10.phx.gbl...
> > > Hello everyone,
> > > I'd like to learn how I should go about moving my linked servers from
on
> > sql
> > > server to another sql server. Has anyone done this before?
> > > Thanks
> > > Chieko
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)