Moving SQL Server to New Machine

Last Post 29 Apr 2002 09:07 AM by Shibly. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Shibly
New Member
New Member

--
29 Apr 2002 09:07 AM
I came to know there are two ways to move SQL Server from One machine to Another.
First:
1. Backup master DB from old Server(A)
2. Start new server (B) in Single User mode
3. Resore master DB to B
4. Restore MSDB and Model to B
5. Then Restore all user DBs to B
In this setting all users (NT and SQL) will be intact and new Server will be exactly like old one.

BUT The Q:
If directory structures in two Servers are different.
In that case when you restore master DB into NEw Server from Old Server it will look for Other DBs in old server's path. And master DB will not be restored properly. And the process will fail.
So How we can address this issue?

Second:
1.
Use user Database in old Server to run this script (you can choose either one) to add SQL login and fix orphan SQL logins.

a.
SELECT 'sp_addlogin '+"'" + name + "'"+Char(13)+'GO'+char(13)
+ 'sp_change_users_login '+"'"+'Auto_fix'+"'"+','+"'"+
name+"'"+Char(13)+'GO'
FRom sysusers --where status=2 and name!='dbo'
WHERE issqluser=1
and uid>3 -- filter out dbo, guest, INFORMATION_SCHEMA
and uid<16382 -- filter out groups/roles, the number is 0x3ffe = 0011 1111
1111 1110

b.
SELECT 'sp_addlogin '+"'" + name + "'"+Char(13)+'GO'+char(13)
+ 'sp_change_users_login '+"'"+'Update_one'+"'"+', '+"'"+ name+"'"+','+"'"+
name+"'"+Char(13)+'GO'
FRom sysusers --where status=2 and name!='dbo'
WHERE issqluser=1
and uid>3 -- filter out dbo, guest, INFORMATION_SCHEMA
and uid<16382 -- filter out groups/roles, the number is 0x3ffe = 0011 1111
1111 1110

c.
select 'sp_addlogin @loginame = ' + name +
', @passwd = "' + password +
'", @encryptopt = skip_encryption' +
char(13) + 'go'
from syslogins
where name not in ('sa')


Copy the result from old Server and run it in New Server.

BUT the Q:

i.. In first two scripts you are missing the Passwords. In this case password will be null.

ii. In the thirst script you are trying to get the password but passwords are encripted in sql. So when you selected passwords from syslogin table you are not getting exact one but encripted one. So when you enter into new Server with skip_encription option then what will be password in new Server.

So how we will address this issue?


2. Use master DB in Old Server to run this script to get NT login info.

SELECT 'sp_grantlogin '+"'"+loginname+"'"+''+char(13)+'GO'
FROM syslogins
WHERE isntuser=1
and name is not NULL


Copy the result from old Server and run it in New Server.



Acceptable Use Policy
---