Difference Between Restore And Attach

Question

Post reply

Difference Between Restore And Attach

Anjan@Sql

SSCrazy

Points: 2316
More actions
September 3, 2013 at 9:53 am

#281958

What is the difference between Attach And Restoring a Database in Sql Server?Looks like a Simple question?please folks share your comments?

Viewing 15 posts - 1 through 15 (of 17 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Restore you take a database backup and SQL creates a database with database files from that backup

Attach you just have the database files (no backup) and SQL creates the database using those files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

In practice, a restore typically has .bak, .trn., and .dif files. The attach usually has .mdf, .ldf, and .ndf files.

SQLAli Ten Centuries Points: 1109 More actions · Answer 3

Shaw,

I think below one is also one of the differences what do you say?

Restore will create the new database and Attach will just create the existing database.

Thanks in advance for your input.

Thanks,
I’m nobody but still I’m somebody to someone………….

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Before you attach, there is no database. Just files in the file system. So no, that's not correct.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

sqlnaive SSCoach Points: 17437 More actions · Answer 5

So we can say

"Attach is used to move a database physically and restore is used to create/modify database as per requirement based on backups"

SQLAli Ten Centuries Points: 1109 More actions · Answer 6

Ok accepted Shaw

If you check the created date i think restored database will be the recent date but attached DB date will be old date.

Thanks,
I’m nobody but still I’m somebody to someone………….

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

sqlnaive (9/5/2013)
"Attach is used to move a database physically and restore is used to create/modify database as per requirement based on backups"

If you like. It's not true though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

sqlnaive SSCoach Points: 17437 More actions · Answer 8

GilaMonster (9/5/2013)
sqlnaive (9/5/2013)
"Attach is used to move a database physically and restore is used to create/modify database as per requirement based on backups"
If you like. It's not true though.

Which part Gail ? Would like to be rectified. 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

sqlnaive SSCoach Points: 17437 More actions · Answer 10

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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, MVP, M.Sc (Comp Sci)
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

sqlnaive SSCoach Points: 17437 More actions · Answer 12

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

You're welcome to your opinions, just don't expect them to held as universal definitions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 14

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.