At work we used to follow the workflow of creating database by scripts, the major advantage of it is that every developer can experiment on their own database, and if they screw the table or database up, it is just one click from Visual Studio to regenerate the database and data.

Scripting the data objects are easy, you can either type in the scripts yourself, or just right click the object at Query Analyzer or SQL Enterprise Manager to create the object. Yet scripting the data could become challenge. My team used to use a lot of insert statements, and became painful to write and slow.

Finally I stumbled upon bcp, a command-line utility that allows you to dump data off a table, or bulk copy a big chunck of data to SQL server without the penalty of logging, which fits perfectly for development purpose. The question comes to, in what order should the data be bulk-copied to tables. Because there are foreign-key constraints, thou shall not populate the child table before the parent table.

After using Enterprise Manager in conjunction with SQL Profiler to find out how dependencies are calculated in SQL Server, I found that sp_MSDependencies is a nifty stored procedure that would list all of the SQL server objects and the sequence. So the top level tables which don’t depend on any other tables would have the sequence of 1.

With that information being available, I was able to write a tool to extract data of an existing SQL Server database, and create a batch file that would bulk copy the data to any other SQL Server database with the same schema.

Advertisements