Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Difference Between Restore And Attach Expand / Collapse
Author
Message
Posted Friday, September 6, 2013 4:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
GilaMonster (9/5/2013)
Attach is not the only way to move a database physically and restore doesn't modify databases (doesn't create them technically, the database was created previously if a backup exists)


Yes Gail. But Attach is one of the way to move the database. While we cannot say the same for restore as it's more like refreshing (in case the db already exists) or create (if db is not there in tar
Post #1492138
Posted Friday, September 6, 2013 4:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
If I'm moving databases from one instance to another, I'll use backup/restore. It definitely is a way to move databases.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1492141
Posted Friday, September 6, 2013 5:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
GilaMonster (9/6/2013)
If I'm moving databases from one instance to another, I'll use backup/restore. It definitely is a way to move databases.


That is obviously one's choice. In my opinion, moving a DB technically is moving one from say A to B such that after the movement only B has the moved database (so the primary database is moved to B and is now not present in A, where detach/attach will work). While with restore your primary copy will remain intact in server A and you are refreshing/creating the DB in server B.
Post #1492161
Posted Friday, September 6, 2013 5:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
You're welcome to your opinions, just don't expect them to held as universal definitions.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1492168
Posted Friday, September 6, 2013 6:45 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 6:07 AM
Points: 1,486, Visits: 1,765
sqlnaive (9/6/2013)
GilaMonster (9/6/2013)
If I'm moving databases from one instance to another, I'll use backup/restore. It definitely is a way to move databases.


That is obviously one's choice. In my opinion, moving a DB technically is moving one from say A to B such that after the movement only B has the moved database (so the primary database is moved to B and is now not present in A, where detach/attach will work). While with restore your primary copy will remain intact in server A and you are refreshing/creating the DB in server B.


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.
Post #1492199
Posted Friday, September 6, 2013 7:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:04 PM
Points: 31,354, Visits: 15,817
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1492245
Posted Friday, September 6, 2013 8:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
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.
Post #1492285
Posted Friday, September 6, 2013 9:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:04 PM
Points: 31,354, Visits: 15,817
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1492288
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse