By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,183 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,183 IT Pros & Developers. It's quick & easy.

Best practice for database migration

P: n/a
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

Nov 17 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
BD

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

Nov 17 '06 #2

P: n/a
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" <ro********@yahoo.comwrote in message
news:ej**********@ss408.t-com.hr...
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

Nov 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.