How to maintain database in a separate (portable) SQL file?

Question

I am creating an application for some user to maintain records in database. For this, I'll have to write SQL query (C#) and create the database, if does not exist, when user starts/installs the application. To make the creation and backup procedure easier, I want to create a separate file for SQL Server that will be used to store data. This file will be included in installation pack and copied to the destination folder to be used by SQL Server.

I've seen that we can create such file but never used like this. Is it possible to accomplish the job I am trying to do?

Answer 1

i think that if each application have its own db you should use or SqlCe or SqlLite

They are a self-contained, serverless, zero-configuration, transactional SQL database engine.

So you don't have to install sql express on every pc. They use a subset of tsql and you can do almost the same thing as sql server

you could embed a file in the application with all the sql command to create the db and then execute it the first time the application start.

Anyway you can distribute your app with the db already created and ready to use: it is just a .sdf file

If you think to distribute your app with clickonce than Sqlce is better becouse clickonce recognize its file format and handle it during the application updates

Answer 2

You could make a backup and then restore it http://www.dotnetspider.com/forum/162986-database-backup-restore-through-C.aspx

Answer 3

If you want to use SQL Server, you can use the Compact Edition: http://msdn.microsoft.com/en-us/data/ff687142

SQL Server CE databases are stored in .sdf files (up to 4GB) that can be shipped with your application. That way, if you want to connect to a full SQL Server database later, you could just change the connection strings in your application config.