Adding database in a sample project .zip or svn repository

Question

Hello,

I often want to send sample apps to friends & colleages that work with a database.

For real projects I'd like to include something in the repository that allows people to recreate the database quickly, to avoid having to depend on server X still being there in a few years.

I'm not really good with MS SQL server management studio, so I wonder what is the easiest, quickest & cleanest process to get a MS SQL 2005+ database in a file that is also very easy to "unpack" into a real database.

The end goal is to let the other guy be able to get the database driven application working as fast as possible.

I really like is the .exe that adds the .Net membership tables to a database of your choise. Really easy to use. Are there any tools available that can do something like that?

Other option I know of is a .bak file but I don't find that particulary easy to build/unpack. (requires too many steps like starting up sql server management studio)

Answer 1

For straight SQL Server, the fasted way is to detach the database, zip it up, and re-attach it on the target server. Backup and restore requires a few more clicks.

If you use SQL Compact 3.5, you don't need to do anything - the database is just a single file that gets deployed with the app. But, functionality in Compact is severely restricted, so this may not be an option in every scenario.

Answer 2

If you provide the database mdf and ldf files you can do a one line Attach command.

If you don't required much from the DB you could try SQL CE which is a no installation engine (just include the assemblies) and your database would be one single file.

Answer 3

Absolute simplest for the end user (rather than you) is to include the entire database as a sql server compact edition. You can even do this without any local install (by placing the relevant dll in the application's private bin path) so it is zero effort.

If they wish to edit the database outside your app the normal sql server tools can do this.

Answer 4

I'm a big fan of the Microsoft SQL Server Database Publishing Wizard 1.1. This application lets you use a simple UI to select a desired database. It then converts the whole DB, both its structure AND data, into a SQL text file. The end user only needs to execute the file in an empty database to re-create everything.

This is a nice method, because it can be used in environments where users don't have direct access to the server to attach mdf files.