Moving a Database from one sever to another.

[dja28562]

I have read that using detach/attach is a good way to move a database from one location to another but my main concern is does that work if you are moving the database to another server instead of another location on the server?

Also, if I were to move the database to another server, would the accounts and permissions be lost?

Thanks.

 

[NoCoolHandle]

Yes.

It is very simple.

Detach, copy to a new location (different server/CD/DVD etc...)

Place on a drive local to the new server

In EM select the database node, right click and specify attach. Point to the location of the MDF file and BAM.

you have your database on a new server.


ROb

 

[DrSql]

Yes.. ist is possible the way you wrote, but its not a safe way to do it.
Best method would be backup the DB from server A and restore on server B. Then I can provide you a script to generate another script for all users.

Dr.Sql
Good Luck.

 

[dja28562]

I have read that if everything is not identical on both servers that there will be problems when I move the database to the other server. Is this true?

When I say everything I am referring to the having the same operating system, service packs, and SQL Server 2000 configuration.

Thanks.

 

[MDXer]

THe most common issue with using detach/attach method is user security. The ID assigned to the user security account is different eachtime you create the account, so it is different on each server. SQLServer uses this identity to associate the DB user with it's security roles. By detaching on server A and attaching on Server B this link becomes invalid. The user will exist in the DB and mayeven exist on the Server but will be seen as to seperate users. This is fixed by dropping the DB user and reassigning it to the DB on the new server. I was once told there is even a sp_ that handles this but don't remember what it is.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck