Sunday, March 11, 2012

Best practice for database migration

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...

RegardsRONIN wrote:

Quote:

Originally Posted by

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|||I would "Detach" the database from the old server and "attach" the databases
on the new server. But as BD explained, you still have to address the users
of the database (ie security).

Oscar...

"RONIN" <ronin_1010@.yahoo.comwrote in message
news:ejl3er$274$1@.ss408.t-com.hr...

Quote:

Originally Posted by

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
>
>
>

No comments:

Post a Comment