How do I initialize a blank database as per a populated dataset?

Question

Initialization would include creating all the required tables, constraints and populating the tables.

edit: Is there already a project doing this?

Answer 1

Create a script that is either part of of the end of your database creation script, or runs after it.

It may typically contain a lot of insert statements to populate reference tables, or similar.

Answer 2

You could take it a step further and write a script to load in a text file.

In this text file you could set up some default styling and then use your script to handle it, for example:

tb_newTable
id pk
name vc 255
email vc 255
-end

Or something. So your handler reads this, recognises tb_ to be table name, then on line by line basis it creates the fields, so id pk could field name id, and primary key, name vc 255 is field called name at varchar 255 etc.. with -end stopping it.

This is theoreticaly mind, and the above won't do squat but it migth give you an idea.

Answer 3

I don't know about specific tools for other databases, but mysqldump does exactly this for MySQL.

Answer 4

If you use MySQL there is a command called MySQLDump. This outputs a copy of a database as a series of text SQL statements to create the tables and fill them with data.

So, you use MySQLDump to create a backup of your working database, then you simply execute the resulting file on your empty database to create and populate it.

The doc can be found here... http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Answer 5

Since yu mention .net as a tag you may be using MSSQL.

If you have VS.NET db edition you can use the database tools to generate scripts for this. You can do schema comparer against a blank database and generate the script for the schema. You can then do the same for the data.

If not there are tools you can get to do this for you. Such as http://sqlmanager.net/en/products/dbextract .

There are other tools as well for this, unfortunately that I cant think of off the top of my head as I use the former mentioned.