Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Moving to a new sql server

Author  Topic 

rmrper99
Starting Member

29 Posts

Posted - 2004-05-04 : 15:08:42
Good afternoon. I am going to be installing a new SQL server and transfering the databases to it. The new SQL Server will have the same IP address as the old one, but a new Server name and Instance name. My thought process was to make backups of my user databases and master and then restore them on the new server. Will this work? What I am really concerned about is the Master database. I do not want to lose any of my users that I have set up. I have read an article on this website about this topic but the author is using the same SQL Server name.

Can anyone let me know if restoring the master will work or what I need to do?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-04 : 15:54:51
It should work as long as the path information to the MDF and LDF files is the same. You'll need to run sp_dropserver and then sp_addserver with local after the restore is done though.

But you can always move your users with isp_Transfer_Logins if that's all you need from master. It's a stored proc that I wrote to transfer logins. It uses a linked server:



----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_Transfer_Logins
--
-- AUTHOR : Tara Duggan
-- DATE : September 24, 2002
--
-- INPUTS : None
-- OUTPUTS : None
--
-- DEPENDENCIES : Tables: syslogins
--
-- APPLICATION(s) :
--
-- DESCRIPTION : This stored procedure transfers logins that exist on the
-- primary database server but not on the secondary database server. For the
-- logins that already exist on the secondary database server, it drops the
-- login on the secondary database server, and then transfers the login from
-- the primary database server to the secondary database server. This
-- synchronizes the passwords.
--
-- EXAMPLES (optional) : EXEC isp_Transfer_Logins
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------

CREATE PROCEDURE isp_Transfer_Logins
AS

SET NOCOUNT ON

DECLARE @login sysname
DECLARE @pwd sysname
DECLARE @new_pwd varchar(255)

DECLARE cur_Users CURSOR FOR
SELECT l.name, l.password
FROM master.dbo.syslogins l
INNER JOIN DTS.dbo.sysusers u ON l.sid = u.sid
WHERE (l.isntname = 0) AND (u.islogin = 1 AND u.isaliased = 0 AND u.hasdbaccess = 1)
ORDER BY u.name

OPEN cur_Users

FETCH cur_Users INTO @login, @pwd

WHILE @@FETCH_STATUS = 0
BEGIN
-- If the login does not exist on the destination server, then add it.
IF ((SELECT count(*) FROM SDDEVSQL1.master.dbo.syslogins WHERE name = @login) = 0)
BEGIN
EXEC SDDEVSQL1.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'QTRACS'
END

-- If the login does exist on the destination server, then synchronize the password.
ELSE
BEGIN
EXEC SDDEVSQL1.master.dbo.sp_droplogin @login
EXEC SDDEVSQL1.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'QTRACS'
END


FETCH cur_Users INTO @login, @pwd
END

CLOSE cur_Users
DEALLOCATE cur_Users

RETURN
GO




Tara
Go to Top of Page

rmrper99
Starting Member

29 Posts

Posted - 2004-05-04 : 17:01:01
Tara, once again you are the SQL Princess! You said that this runs on a linked server, so does that mean that I can't run it if I am going to bring down the first server before I put up the second. See, I want to use the same IP address so I can't have them both on at the same time.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-04 : 17:03:00
Yes they would both have to be on at the same time. But just temporarily use a different IP address to move the users over. On a system where we use SQL authentication, we have over a thousand users. It takes about 10 minutes for the stored procedure to run. So it's slow due to the looping, but it's much easier than restoring master or manually moving the users.

Tara
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-07-24 : 14:02:30
Could someone please verify that i want to run this SP on the source server, and have the linked server as the destination? Also where in the script do i define the linked server object?
Go to Top of Page

jstormoen
Starting Member

30 Posts

Posted - 2006-07-24 : 14:15:35
Not along the same lines but as long as the System DB's will reside in the same place (directory structure) on the new machine you could just shut down SQL services on the current server and move all the .mdf's and .ldf's into place and all would startup on the new server.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-24 : 14:15:43
quote:
Originally posted by duhaas

Could someone please verify that i want to run this SP on the source server, and have the linked server as the destination? Also where in the script do i define the linked server object?



I don't recommend using this script anymore. It works fine, but there's an easier solution:
http://www.sqlmag.com/articles/index.cfm?articleid=16090&

Tara Kizer
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-24 : 14:22:46
quote:
Originally posted by jstormoen

Not along the same lines but as long as the System DB's will reside in the same place (directory structure) on the new machine you could just shut down SQL services on the current server and move all the .mdf's and .ldf's into place and all would startup on the new server.



Yes that would work. That's what I mentioned in the first part of my post 2 years ago.

Tara Kizer
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-02-01 : 11:29:38
Because this thread is linked from the FAQ, for completeness this link is also useful:

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k

Copied from: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78399 - thanks Tara

Kristen
Go to Top of Page
   

- Advertisement -