Moving Database to another server

[cceng]

I know what utilities to use for moving the database, but I am not sure if I should move all database files or just the ones I am moving.

ex.. I am moving toffice database. What db files would I move. Do I need to move all including the master?

The server I am the database's to is a new server and is only housing these temp till we rebuild the current one.

any idea's are greatly appreciated.

 

[AshleyRickards]

I would suggest backing up on current server and restoring to your temp server. Should work for you. Ashley L Rickards
SQL DBA

 

[cceng]

Thanks for reply,

Does that mean I should restore everything or just the sql directories?

 

[AshleyRickards]

If you server is primarily for SQL then I would backup all production databases using Enterprise Manager. Then restore them to your temp server.

OR (in case I'm not understanding you)

Simply run an SQL backup on production datatbases only not system db's(Enterprise Manager). Move the .BAK files to temp server. Rebuild server and reinstall SQL. Move files back to server and restore to the fresh install.

Ashley L Rickards
SQL DBA

 

[cceng]

This is a new idea then the one I was going to use. I was going to detach the databases and move them to the new server.

Do you feel they way described by you is more effective then detaching the db's?

Thanks!

 

[AshleyRickards]

I'm not sure what you mean by "Detach" how would you re-attach? If you've done this before then it sounds OK but the method I described would ensure integrity for the restored db's and system db's on the temp server install. Ashley L Rickards
SQL DBA

 

[cceng]

The command sp_detach_db is used to detach the datbases. Then you mave the files to the new server and use the command sp_attach_db to attach them to the temp server.

Thats why I am not sure what files I am suppose to move to the temp server. We would like this temp server to be operational just in case the rebuilding of the existing server does not go as planned.

This is the method I was recommended to me.

 

[AshleyRickards]

OH I see. That will work fine. I'm more of a GUI than command line.

The actual files that need to be moved over are databasename.MDF and .LDF Ashley L Rickards
SQL DBA

 

[cceng]

Sorry for another reply, but one last question.

Do I have to move all the DB files or just the one's we created? I am guessing I don't need to move the master and other system db's.

 

[AshleyRickards]

That's correct. The system db's will be created when you install SQL. Ashley L Rickards
SQL DBA

 

[cceng]

Thanks for all the help!