Move one databse from one server to other one, and renamed it

Question

Move one databse from one server to other one, and renamed it

Junior_DBA

SSCrazy

Points: 2002
More actions
July 21, 2008 at 5:53 pm

#195868

Hi all,
I perform the following to move one databse from one server to other one by using SSMS:
1) offline DB
2) detach DB
3) copy/paste all mydb.mdf, and mydb.ldf files
4) attach DB
5) in the database/rename properties....change name,......click ok.
Are they ok?...do I have to do something else?????
Please I need some help...I have to move one PROD DB and I feel confusing with this.....do I have to move master,msdb, model..files????
Please help..
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 1

once u complete attach/rename, u need to fix the users for the db, as there will be an SID mismatch since you have moved it to a diff server.

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 2

[font="Verdana"]Checkout the below link to fix orphan users

How to fix orphaned SQL Server users

Why do you need to move system db's?? Are you migrating your server??

If you are need only user db, you can detach it and attach it in the destination server.

Master db contains logins information, if you need to move logins from one server to another use sp_help_revlogin procedure.

MSDB db contains information about jobs, DTS packages etc.[/font]

Regards..Vidhya Sagar
SQL-Articles

Junior_DBA SSCrazy Points: 2002 More actions · Answer 3

Hi,

My new server is already running SL Server 2005 with a database created before, what I intent to do is migrate my PROD database existing in one server to this new one.. The migration is because the first server is old and small, and we bought a new, big one server and we want to centralize our databases..

The thing is if in this new one already exists a master, msdb files, how can I move the ones that I migrating, if I do copy paste, that means that I will replace the other ones....but what happend with the jobs , and everything from the database already running???where I put the ones belonging to the db migrating...I am confused.

Please any help?

Thanks.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Waseem Jaleel (7/21/2008)
once u complete attach/rename, u need to fix the users for the db, as there will be an SID mismatch since you have moved it to a diff server.

Ya huh... and how do you do that? 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 5

The users can be fixed by running a procedure which runs something like a cursor, the sp sp_change_users_login on each user of the database so that the SID mismatches are resolved.

Perry Whittle SSC Guru Points: 233792 More actions · Answer 6

Junior_DBA (7/21/2008)
1) offline DB
2) detach DB

for future reference;

once the database is marked offline you do not need to detach the database you can just go ahead and copy the files straight away.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

michael.anyone Ten Centuries Points: 1071 More actions · Answer 7

i like the sp_help_revlogin

with this SP you can generate a script with all userlogins,sid,password ...

than you only need to bring the needed scriptlines to the new server and run it in query of the master db

bye

Michael