2

I always see this option called attach database in SQL Server. Why would someone attach a database? Why not just restore a database from the .BAK file? I guess I don't see the purpose of it.

What would be the result of attaching it? It's quicker than a restore, but what is the technical result? Can you still alter the database? Add data to it? The preferred method or not recommend it?

I have seen Attach/detach vs. backup/restore but nothing in there explains the purpose of attaching or detaching. It basically doesn't even touch on it.

8

Attaching/Detaching could be quicker than backup/restore depending on the situation.

A detach is instantaneous and you can start copying the files right away, and the attach is near instantaneous too. Backup takes a while, and restore takes a while too.

This technique was a lot quicker before we had backup compression because the size of the files that needed to be copied over were more or less the same size depending on the situation.

Backup compression has an influence on that, since the backups are now a lot smaller than the data files.

It all depends on your IO speed and the source and destination of the copy.

The math is fairly easy, If the time of taking a backup, copying over the files, and restoring the backup is shorter than copying over the mdf+ldf files it's faster to backup/restore, otherwise it's faster to detach/attach.

The detach/attach method obviously only works if your database can go offline.

If your IO subsystem is fast, and the network link over which you have to transfer the database is slow it might be faster to transfer a compressed backup (if that's available in your version/edition). If your IO subsystem is slow and so is the process of backing up and restoring but you can copy the files over quickly it might make sense to detach, copy over the relatively larger .mdf files and reattach.

In my opinion, this used to be more of an issue with large databases before we had backup compression available. If I recall correctly this was a possible question in Microsoft Certification exams before we had compression but I haven't seen it in the material for more recent versions.

The end result is basically the same, an exact copy of the database.

4

Detach-Attach can be used for various purpose:

  1. Moving the data/log files to a different disk with more space or as per requirement.
  2. When detached the data/log file can be copied and moved to another server/instance serving a purpose for quick replication of database in another instance.
  3. Sometimes in database migration when the downtime is very limited you can go ahead with this technique as it saves time.
4

Community Wiki answer based on comments left on the question:

I used detach/attach for moving data/log file to a different storage device or to a different LUN (for a number of reasons). See the Stack Overflow Q & A SQL Server backup/restore vs. detach/attach which has a compare and contrast between the two methods.

Once you attach database it is like any other online, multi-user database. It is not a matter of preference, rather what you need. Read the answer I mentioned above. A common example, if I need to move a file to a bigger drive. - SqlWorldWide

Related Q & A:

  1. How do I attach a database in SQL Server?
  2. Attach/detach vs. backup/restore
  3. Should I migrate data using detach/copy/attach or through backup-restore-replay?

Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.

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.