Dettach/Attach DB

Question

Dettach/Attach DB

Mike Levan

SSChampion

Points: 14201
More actions
November 19, 2008 at 1:57 pm

#154931

I have a database EMP on server 1 which is old and same database EMP is on server 2 which is new. I want to update my old one with new one without any downtime, what would be the best approach.
Rename EMP to EMPold on server 1,Dettach EMP on server 2 and Attach EMP on server 1, Drop EMPold.
does it work, if i rename a database i dont think database files get renamed then i might have problem attaching EMP as there is already one.

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

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

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

I guess it depends on your definition of no down time. Based on how you are talking I assume you mean minimal time. What is the size of the database?

I would think that detaching on one server, copying the files to another directory if they share the same physical name and then reattaching the database with the copied files would suffice. You would also need to rename the old db as you stated.

As a side note you may have issues with accounts, jobs, etc that are tied to the db/server as SIDs may be different. So you may have to script some of these items.

Other options include SSIS which has a move db option that has an offline and online option. It allows you to also send over accounts and such so you can do it as one process. However I have never had great luck with this option.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

Nope file won't get renamed by this. Downtime is still there as you are going to rename the existing database. Better detach the existing one and rename its files and attach the new one.

MJ

Mike Levan SSChampion Points: 14201 More actions · Answer 3

I am moving files to a different drive in the same instance at then attaching the DB. to replce the old database can i make the old one offline and attch new one as both has same name, does it work?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Nope it won't work, you need to rename the existing one.

MJ

Mike Levan SSChampion Points: 14201 More actions · Answer 5

which is faster and best(performance) approach when compared with moving database files and attach or restoring backup file.

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

Mike Levan (11/20/2008)
which is faster and best(performance) approach when compared with moving database files and attach or restoring backup file.

i have found detach, copy and re attach generally quicker

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

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