Difference Between Restore And Attach

Question

Difference Between Restore And Attach

Viewing 3 posts - 16 through 17 (of 17 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

batgirl (9/6/2013)
In my opinion, either process can be used to move or copy a database and neither has anything to do with whether the primary copy remains intact.

+1. Either is for moving, and in the case of DR, the primary certainly doesn't remain intact.

I'm not sure what the point is here. From a technical definition, they work differently. From a "which accomplishes what", it feels like you're playing some semantic game.

sqlnaive SSCoach Points: 17437 More actions · Answer 2

Steve Jones - SSC Editor (9/6/2013)
batgirl (9/6/2013)
In my opinion, either process can be used to move or copy a database and neither has anything to do with whether the primary copy remains intact.
+1. Either is for moving, and in the case of DR, the primary certainly doesn't remain intact.
I'm not sure what the point is here. From a technical definition, they work differently. From a "which accomplishes what", it feels like you're playing some semantic game.

Steve, back to the original question of this discussion "difference between Attach And Restoring a Database in Sql Server". I just wanted to say that in detach/attach method, primary source is moved to target while in backup/restore, as you are using the backups of the source to refresh/build destination, the source will remain intact. Don't know if I'm putting it wrong way.

In between, Gail/Steve, I've respect for this forum and all the SQL leaders here who are here to clear our doubts. I was putting forward my point as a discussion to clarify if I'm wrong not as questioning you or anyone.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

You are putting it wrongly. You're implying that a backup/restore is a copy and an attach/detach is not. That's not correct.

I can:

- detach a database, copy the mdf/ldf/ndf files to a new instance/folder/drive/company, attach the files to a new instance with a new name. I can attach the original files to the original instance/db name.

- I can backup a database, copy that file elsewhere, have the first instance fail/retired, restore on a new instance. Move.

The difference is that an attach/detach removes the data/log files and database from the instance, or adds the data/log files and database to an instance. It requires the full size of the files, and all files to exist. It requires one copy of the files to move/copy the data to a new instance.

A backup allows the original to continue to be used while a copy of the data and objects, and only a copy of those objects, is made. This is usually less space than the database file sizes. If you copy this to another instance, to get it to a database (new/refresh), you must do another copy in which the data is copies into the mdf/ldf/ndf files.

Stating anything else about moving, copying or "why" you do something, is incorrect or incomplete. It's also unnecessary. The differences are technically how things are handled, not the purpose or necessarily the manner in which the technique is used.