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
Moving to a new sql server
Posted - 05/04/2004 : 15:08:42
