SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Moving to a new sql server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmrper99
Starting Member

29 Posts

Posted - 05/04/2004 :  15:08:42  Show Profile  Reply with Quote
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

USA
37453 Posts

Posted - 05/04/2004 :  15:54:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 05/04/2004 15:55:21
Go to Top of Page

rmrper99
Starting Member

29 Posts

Posted - 05/04/2004 :  17:01:01  Show Profile  Reply with Quote
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

USA
37453 Posts

Posted - 05/04/2004 :  17:03:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 07/24/2006 :  14:02:30  Show Profile  Reply with Quote
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

USA
30 Posts

Posted - 07/24/2006 :  14:15:35  Show Profile  Reply with Quote
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

USA
37453 Posts

Posted - 07/24/2006 :  14:15:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
37453 Posts

Posted - 07/24/2006 :  14:22:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 02/01/2007 :  11:29:38  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000