Sql-server – the purpose of attaching a database

attachsql server

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.

Best Answer

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.