Difference Between Restore And Attach

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

  • 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
  • In practice, a restore typically has .bak, .trn., and .dif files. The attach usually has .mdf, .ldf, and .ndf files.

  • 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………….

  • 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
  • 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"

  • 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………….

  • 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
  • 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. 🙂

  • 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
  • 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

  • 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
  • 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.

  • 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
  • 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