|
|
Posted by BD on 11/17/06 23:10
RONIN wrote:
> Can you tell me what is the best practice for SQL database migration from
> one DB server, to another one, new DB server. The old DB server will be
> removed.
>
> 1. Backup from old and restore all databases on the new server
> 2. Export data and copy/import on a new server
> 3. Something else...
>
> Thanks in advance for any good advice...
>
> Regards
Before exporting, create a SQL script containing all users and roles.
SQL Server's database user IDs are 'connected' to an OS logon or a SQL
Server ID. But in fact it's the under-the-hood SID that it is linked
to.
So when you restore the db on the new server, and check the Users
container, you will likely see some UserIDs without Logons. They're
effectively Orphaned.
You'll need to remove those users from the db, and re-add them. Either
manually, or with a script generated on the source db.
In my case, I have a stored procedure which drops all users that lack a
corresponding Logon.
My 2 cents...
BD
Navigation:
[Reply to this message]
|