migrating to different server part II

[Lena]

I have another question regarding migration process to diffrent SQL Server.

Last time when I did migration I didn't restore system databases (master in particular).
I only restored user databases and of course I've got login errors such
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
I just used sp_change_users_login to fix those orphans.

I've read articles in MS knowledge base and not quite sure is this a good idea to restore master database prior to restoring all user databases on the new server in order to keep all logins?

I'll try to use recommended article for transfering logins with password instead of restoring master database from backup
http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP

Any advice is greatly appreciated.
Lena

[Franco]

If you can restore the Master DB, then you have all your logins and passwords because they are stored in sysxlogins table.
Regards - Franco

[Dmitri]

Hi,
It's depend on what you need (in terms of migration). If you create the mirror of you server it's a good idea to restore the Master db. If you transfer you database (for example just one db MYDATABASE) to the existing server where the other databases exists then Master restore operation is not applicable.
Just keep in mind that LOGIN and USER in SQL Server it's not the same. USER's names stored in you user db. LOGINs stored in master db. Then you have to choose the way how to transfer the logins (users) (restore master or use the script) from one server to another, just realize for youself what the purposes of you migration (see above)...

Regards,
Dmitri

------------
Lena at 6/28/01 11:35:25 AM

I have another question regarding migration process to diffrent SQL Server.

Last time when I did migration I didn't restore system databases (master in particular).
I only restored user databases and of course I've got login errors such
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
I just used sp_change_users_login to fix those orphans.

I've read articles in MS knowledge base and not quite sure is this a good idea to restore master database prior to restoring all user databases on the new server in order to keep all logins?

I'll try to use recommended article for transfering logins with password instead of restoring master database from backup
http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP

Any advice is greatly appreciated.
Lena