SQL Server 2005 Script? Replication? Backup/Restore?

[choppaz]

I am developing an application (C#) that needs to create a new instance of a database in the current working folder each time the program is run. It will need to be an empty database - no data, just table structures, stored procedures, etc.

So, each time it's run, the database will be created in the current working folder and populated with data from that instance of the run. C# application is going to do the work of populating the database tables. It needs to only contain data from that run, and should be unaware of the other instances of the databases in other folders.

So, the question is, how is the best way to implement this? The database currently has 90 tables, and we will need to re-create each one of them for each instance. I am considering generating a script or something from within SQL Server 2005, then running it from the C# code to create a new database when the C# application is run.

Is generating/running a SQL Server script the best approach, or would it be better to backup the empty database and restore it to the current working folder? Or to use replication (does replication make sense for empty tables, or is it only applicable to keeping data in sync across instances of the database?)

Thanks!

[hspc]

I use a script generation to create databases in the installers and this works fine.
Backup and restore is a valid good option too.
Replication is not used for this kind of scenarios.

[Alsvha]

If the database is located on a dedicated database server, you could also modify the model system database to include all the tables et al you need, and then all subsequent create databases will be created with those tables.
(Or rather you don't need it to be dedicated, but all new databases are created based on the model database, so the approach is safer with a dedicated system)

Otherwise, I'd go with either scripting or restore an empty database.

[davide++]

So, each time it's run, the database will be created in the current working folder and populated with data from that instance of the run. C# application is going to do the work of populating the database tables. It needs to only contain data from that run, and should be unaware of the other instances of the databases in other folders.



Hi all.
I don't know exactly if there're some particular reasons that require you have to create a database each time your application runs, but this sounds strange for me.
Usually applications connect to a database, which is created once, and insert records into tables. If you want to distinguish the runs, you can add a column that contains the "run id".
Moreover, probably the database creation will take a long time, whether you use scripts or not.