MS SQL Server - Moving SQL Server

Author

Moving SQL Server

Tedmond

2006-12-06, 12:12 am

Dear all,

I have two questions

1. What is the best way to move a whole SQL server to another machine with
different machine name?

2. I tried backup all DB including master from one server then restore all
to another server. However, I found all the users in individual database are
invalid but the Logins in SQL Security are ok. Why and how to solve this?

Thanks for any help.

Tedmond

Hari Prasad

2006-12-06, 12:12 am

Hi,

1. What you did is exactly the good method..

2. If you restore the Master database followed with all databases then the
Logins will sync with Users in database. Incase if you find any descripancy
then
use the system procedure sp_change_users_logi
n (see books online) to sync
the Logins with database users...

Thanks
Hari

"Tedmond" <Tedmond@discussions.microsoft.com> wrote in message
news:030841E2-E09B-4F32-B630- AEF5D6B2392C@microso
ft.com...
> Dear all,
>
> I have two questions
>
> 1. What is the best way to move a whole SQL server to another machine with
> different machine name?
>
> 2. I tried backup all DB including master from one server then restore all
> to another server. However, I found all the users in individual database
> are
> invalid but thogins in SQL Security are ok. Why and how to solve this?
>
> Thanks for any help.
>
> Tedmond
>

Arnie Rowland

2006-12-06, 12:12 am

These articles may help:

Moving Server/Database/Logins
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...irdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles for
SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scr...utions/1598.asp Script Roles
and Permissions

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

"Tedmond" <Tedmond@discussions.microsoft.com> wrote in message
news:030841E2-E09B-4F32-B630- AEF5D6B2392C@microso
ft.com...
> Dear all,
>
> I have two questions
>
> 1. What is the best way to move a whole SQL server to another machine with
> different machine name?
>
> 2. I tried backup all DB including master from one server then restore all
> to another server. However, I found all the users in individual database
> are
> invalid but the Logins in SQL Security are ok. Why and how to solve this?
>
> Thanks for any help.
>
> Tedmond
>

Sponsored Links