What are the advantages/disadvantages between distributing a SQL Server database as a .bak or an .mdf?

We are working on generating a database that will be distributed to several third parties. We will also re-generate this database on an on-going basis, redistribute it, and those third p开发者_Python百科arties will need to overwrite their existing database copy with the new version.

I'm trying to decide whether I want to detach the database temporarily, make a copy of the .mdf, and send that copy out, or whether I should just do a full backup of the database, and send the .bak out.

The primary difference I can see is that to distribute the .mdf, you must detach the database temporarily, so that you can copy it.

What are the other pros/cons of each format?

Are there security implications with distributing one over the other?

Is it easier to initially import one format over the other?

Thank you.

---

Neither. The proper way to distribute database changes is via upgrade scripts, otherwise those third parties using the database will loose the actual data contained in the database.

For the case when the data is never changed by the third parties (ie. the database is read only at those sites) then distribution by backup file is feasible. MDF is completely out of the question, first and foremost because MDF is not the entire database: at least the LDF is required in addition to recreate a coherent database. Simply attaching the MDF w/o a corresponding LDF will result in most cases, in a corrupt database. In addition to being incorrect, MDF distribution is inefficient (BAK files are smaller than the corresponding MDF because they do not contain unallocated pages) and also MDF manipulation requires placing the database offline during the file copy.