September 3, 2013 at 9:53 am
What is the difference between Attach And Restoring a Database in Sql Server?Looks like a Simple question?please folks share your comments?
September 3, 2013 at 9:56 am
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
September 3, 2013 at 2:59 pm
In practice, a restore typically has .bak, .trn., and .dif files. The attach usually has .mdf, .ldf, and .ndf files.
September 5, 2013 at 6:52 am
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………….
September 5, 2013 at 7:08 am
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
September 5, 2013 at 7:15 am
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"
September 5, 2013 at 7:30 am
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………….
September 5, 2013 at 8:42 am
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
September 5, 2013 at 8:51 am
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. 🙂
September 5, 2013 at 8:54 am
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
September 6, 2013 at 4:07 am
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
September 6, 2013 at 4:12 am
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
September 6, 2013 at 5:05 am
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.
September 6, 2013 at 5:24 am
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
September 6, 2013 at 6:45 am
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.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply