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 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

No comments:

Post a Comment