April 24, 2008 at 2:24 pm
Does anyone have some simple steps I can follow, I did some reading and discovered that database backup and recovery is the best way, also recoving the master, msdb, and the model then the rest of the databases is a good solution. Please help. I am migrating a sql 2000 to a new sql 2000
April 25, 2008 at 5:23 am
Is the server you are migrating to going to have the same name?
Also is the directory/file structure the same? If it is all you would need to do is copy the database files (system and user).
Otherwise you would be best to backup and restore.
I have done this a few times now, and this link has some good pointers http://vyaskn.tripod.com/moving_sql_server.htm
Kev
April 25, 2008 at 5:56 am
Be careful of orphaned users......
When you restore your database, or even detach and attach the user databases, the old ids for the users will be kept, while the logins created at the server level will have different IDs. Hence, even though it may seem that the Logins and Users are the same, they are actually different, causing malfunction.
Check out the orphaned users using the following script -
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND sid is not null
AND sid <> 0x0
AND suser_sname(sid) is null
ORDER BY name
......and correct them using the following script -
EXEC sp_change_users_login 'update_one', 'Username', 'Loginname'
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 25, 2008 at 6:04 am
Right click the source server->All Tasks -> Copy database wizard
Why dont you use this
April 28, 2008 at 3:51 pm
I put the sqlserver in single user mode to restore the master, now I cannot put it back to multi user mode. I used the -m in the startup to put server into single user mode it worked I restored the master, I restarted the sql server, I was then going to restore the msdb by putting that database in single user mode then recover I get a error stating server is in single user mode and I can't recover the msdb.
Any help?
Mark
April 29, 2008 at 6:58 am
Chandrachurh Ghosh (4/25/2008)
Check out the orphaned users using the following script -
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND sid is not null
AND sid <> 0x0
AND suser_sname(sid) is null
ORDER BY name
Is this better than sp_change_users_login 'REPORT'? Or any different?
(don't have any orphaned users to test this on, at the moment)
"Got no time for the jibba jabba!"
-B.A. Baracus
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply