How to deploy my application if it requires a database with tables created?

Question

I was previously using a database in SQL Server CE. That's kind of easy, because I can have a database in a simple .sdf file and deploy it with my application (either empty or with data already added).

But what if I want to change the database to a SQL Server Express one?

Of course, if my client already has the database I need, I don't have to modify anything (well, maybe the connection string).

What should I do if I want to deliver an application to a client that requires a local SQL Server Express database created? Is there a way of doing this with .mdf files?

Of course, I have the SQL code for creating all the tables, with the corresponding foreign keys, etc, in case it is necessary.

I suppose that is something lot of developers have faced with, but I don't really know how to do it.

Please, do not suggest using SQLite, MySQL, or other database. I'm asking this question because I want to know how to do it particulary on SQL Server.

Answer 1

You can connect to an SQL Express with attached DB file:

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

Also you can backup your db and then restore it when deploying.

UPDATE But if you don't want to copy data, only structure, I suggest that you should go on with SQL scripts. You can check if the DB exists and create it if it is not. You can even init some tables with bulk inserts.

if db_id('dbname') is null BEGIN CREATE DATABASE ... etc

Depending on type of your application you can either specify your scripts for deployment (AFAIK, VS2010 offers this for web applications) or call sql.exe with your script as input as a deployment step.

Answer 2

There are several things to think about:

1. You might need to redistribute SQL Express itself
2. Deploy the database to a fresh setup

3. Migrate an existing database to the new schema/version

   Add 1. A long but very descriptive doc about this procedure The official docs

   Add 2. On the MSDN

   Add 3. The only two ways I have used to solve this are 1: deploy the new version and migrate the data using a script and 2: make a script that transforms the current version into the new version.

In my experience migrating an existing database can prove to be the hardest thing depending on the changes to the database schema.