0

Sql server attach prerequisite states that a db must be detached before trying to attach it to another instance (https://docs.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver15#Prerequisites)

However this migration link (https://docs.microsoft.com/en-us/sql/database-engine/install-windows/choose-a-database-engine-upgrade-method?view=sql-server-ver15#migrate-to-a-new-installation) recommends an attach without any mention about detach. Example:

enter image description here

What is the implication at attempting an attach without detach?

1 Answer 1

2

However this migration link recommends an attach without any mention about detach.

You are not reading it correctly, the attach here means any database for which mdf file is present. A classic case here is stopping the database and moving the mdf files and then attaching it, this actually avoids detach. But I seldom use this method

  1. It changes database owner
  2. Many times specially with SQL Server 2005/2008 i have faced database corruption while stopping DB, moving mdf files and attaching.

Similar thread

Nothing beats good backup and restore.

6
  • Another thing I'm confused about it that - since the db paths are held in the master db, then why is the diagram asking for an attach. When the LUN is presented to the system, why won't it auto create the dbs?
    – variable
    May 9 at 8:08
  • For user databases, if you attach a LUN with location of mdf files different than that of source server the database would fail and DB will be in recovery mode. In this case you have to run alter database command to manually change the location and DB will come online
    – Shanky
    May 9 at 8:23
  • Say on source server the data and log is on E and F drives respectively. Now on new server when I present these drives after restoring the master db, then shouldn't it auto detect the dbs? I'm not sure why the diagram is asking for attach. What you think please?
    – variable
    May 9 at 8:32
  • If the drive letter and disk configuration on destination is same as source attach WILL work just fine. If they are not same attach will fail.
    – Shanky
    May 9 at 8:37
  • Good answer. Note that a database restore to a new database will also change the database owner to whomever restored the database. In either methodology this is an easy enough to fix in the database's options GUI.
    – J.D.
    May 9 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.