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

Question

We are working on a database that will be distributed to several third parties. We will also re-generate this database on an ongoing basis, redistribute it, and these third parties will need to overwrite their existing copy of the database with the new version.

I am trying to decide if I want to temporarily temporarily disable the database, make a copy of .mdf and send this copy, or just make a full backup of the database and send .bak from.

The main difference that I see is that in order to distribute .mdf you must temporarily disable the database so that it can be copied.

What are the other pros and cons of each format?

Are there any safety implications when distributing each other?

Is it easier to import one format over another first?

Thanks.

Answer 1

None. The correct way to propagate database changes occurs through upgrade scripts , otherwise third-party users using the database will lose the actual data contained in the database.

In the case when the data is never changed by third parties (i.e. the database is read only on these sites), then distribution to the backup file is possible. MDF is completely out of the question, primarily because MDF is not the whole database: at least LDF is required in addition to reconstructing a consistent database. A simple MDF connection without the appropriate LDF will in most cases result in a damaged database. In addition to misallocation, MDFs are inefficient (BAK files are smaller than corresponding MDFs because they do not contain unallocated pages), and MDF manipulation also requires that the database be taken offline while the file is being copied.