Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Best practice to restore or move a database?

Status
Not open for further replies.

yipster

IS-IT--Management
Joined
Feb 21, 2005
Messages
234
Location
US
Hi,

I was wondering which is the best way to move or copy a database to another server while retaining all info.

Backing up database and restoring it or detaching the database then copy or move it to the other sql server and reattching it.

Much thank
 
Hi yipster,

Best thing to do if you are allowed downtime from the origin DB is to detach it, copy the mdf / ldf (and ndf if applicable) to the new server and then attach the db (attach to the new server and don't forget to re-attach on the origin server!!!).

Check out detaching databases in BOL.

HTH

M.
 
I would have to disagree. Detaching a database should be used as a last resort. If you can backup and restore the database that is always the better option.

Detaching and reattaching can change the database ownership, database id, and security mappings of the users within the database. All of which can cause problems the next business day.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Thanks Denny - didn't know that. I guess I've been lucky whe I've done it!

Cheers,

M.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top